[Solved] SUM specific cells based on *text* values

Discuss the spreadsheet application
Post Reply
Doctor J
Posts: 3
Joined: Fri May 17, 2019 1:02 pm

[Solved] SUM specific cells based on *text* values

Post by Doctor J »

Based on a similarly titled recent thread, i'm trying to automate some calculation that is currently done by hand. The problem is, the column that is being used for Criteria contains a character rather than a number. The non-working version looks like:

Code: Select all

=SUMIF(I3:17,'S',G3:G7)
but that gives me a 502 error. Column 'I' contains either 'S', 'M' or 'L'. I searched through the text and number functions and didn't see anything helpful. How can i fix my Criteria?
Last edited by Doctor J on Thu May 30, 2019 11:36 am, edited 1 time in total.
OpenOffice 5.2.7.2 Debian Stretch
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Post by RoryOF »

In OpenOffice, your formula worked for me using ; in place of , and " in place of '
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SUM specific cells based on *text* values

Post by robleyd »

Try

Code: Select all

=SUMIF(I3:17;"=S";G3:G7)
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Post by RoryOF »

I didn't need the = sign. Try both with and without it.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: SUM specific cells based on *text* values

Post by gerard24 »

RoryOF wrote:I didn't need the = sign. Try both with and without it.
Both gives the same result in 99.99% of cases.
So, the = sign is unnecessary.

But there is the remaining 0.01%.

Look at this case :
Sum without or with literal < sign
Sum without or with literal < sign
SUMIFwith=sign.PNG (9.41 KiB) Viewed 2889 times

Code: Select all

=SUMIF(A1:A5;"<20";B1:B5)
will sum if cells in column A are less than 20.

Code: Select all

=SUMIF(A1:A5;"=<20";B1:B5)
will sum if cells in column A are equals to text "<20"
LibreOffice 6.4.5 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Post by RoryOF »

@gerard24: the original question was matching a character in the first column, not a number.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
gerard24
Volunteer
Posts: 958
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: SUM specific cells based on *text* values

Post by gerard24 »

RoryOF wrote:@gerard24: the original question was matching a character in the first column, not a number.
I know I am off topic, it was just about the use (misuse) of = sign in SUMIF/COUNTIF.
That's why I wrote :
gerard24 wrote:So, the = sign is unnecessary.
LibreOffice 6.4.5 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Post by RoryOF »

Always good to have all aspects of a problem discussed.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SUM specific cells based on *text* values

Post by robleyd »

In the situation demonstrated by Gerard24, if you actually want to sum the values where column A is less than or equal to (number) 20 then simply reverse the order of the operators = and <.

Code: Select all

=SUMIF($A$1:$A$5;"<=20";$B$1:$B$5)
sumif.png
sumif.png (5.91 KiB) Viewed 2865 times
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Doctor J
Posts: 3
Joined: Fri May 17, 2019 1:02 pm

Re: SUM specific cells based on *text* values

Post by Doctor J »

robleyd wrote:Try

Code: Select all

=SUMIF(I3:17;"=S";G3:G7)
All right, so i put in the equals sign and double quotes. I tried the semicolon, but they keep getting changed back to commas. In any case, it still gives me the same error. I assume this is a variation between OOo and LibreOffice? This is the my first time with LO, should i take this issue to them??

Code: Select all

=SUMIF(I3:17,"=S",G3:G7)
OpenOffice 5.2.7.2 Debian Stretch
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Post by RoryOF »

Leave out the = sign.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM specific cells based on *text* values

Post by Villeroy »

Doctor J wrote:All right, so i put in the equals sign and double quotes. I tried the semicolon, but they keep getting changed back to commas.
RoryOF wrote:Always good to have all aspects of a problem discussed.
We are dealing with LibreOffice and English user interface. It accepts the semicolon but replaces them with comma. Semicolon is the true separator which is saved in the document but LibreOffice with English user interface uses the comma due to all the English Excel experts which is why the semicolon is "translated" to a comma for the user interface only.

There are at least 6 different ways to do this. SUMIF, SUMPRODUCT, DSUM, SUBTOTAL with filter, Data>Subtotals and pivot tables. I always use pivot tables which display all sums (counts, mins, maxs, averages,...) for every ocurring text value.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Doctor J
Posts: 3
Joined: Fri May 17, 2019 1:02 pm

Re: SUM specific cells based on *text* values

Post by Doctor J »

Gah! So, the real reason this has been failing has nothing to do with the "S". The character after the first colon somehow ended up being the number one, not the letter "I". What's weird is that when i was editing the formula, the correct cells in column "I" were highlighted. That made the problem easy to overlook.
OpenOffice 5.2.7.2 Debian Stretch
Post Reply