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

Discuss the spreadsheet application

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

Postby Doctor J » Fri May 17, 2019 1:40 pm

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   Expand viewCollapse view
=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
Doctor J
 
Posts: 3
Joined: Fri May 17, 2019 1:02 pm

Re: SUM specific cells based on *text* values

Postby RoryOF » Fri May 17, 2019 1:47 pm

In OpenOffice, your formula worked for me using ; in place of , and " in place of '
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29460
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Postby robleyd » Fri May 17, 2019 1:52 pm

Try
Code: Select all   Expand viewCollapse view
=SUMIF(I3:17;"=S";G3:G7)
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2947
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SUM specific cells based on *text* values

Postby RoryOF » Fri May 17, 2019 1:53 pm

I didn't need the = sign. Try both with and without it.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29460
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Postby gerard24 » Fri May 17, 2019 7:19 pm

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 :

SUMIFwith=sign.PNG
Sum without or with literal < sign
SUMIFwith=sign.PNG (9.41 KiB) Viewed 785 times


Code: Select all   Expand viewCollapse view
=SUMIF(A1:A5;"<20";B1:B5)

will sum if cells in column A are less than 20.

Code: Select all   Expand viewCollapse view
=SUMIF(A1:A5;"=<20";B1:B5)

will sum if cells in column A are equals to text "<20"
LibreOffice 6.2.4 on Windows 10
gerard24
Volunteer
 
Posts: 948
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: SUM specific cells based on *text* values

Postby RoryOF » Fri May 17, 2019 7:22 pm

@gerard24: the original question was matching a character in the first column, not a number.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29460
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Postby gerard24 » Fri May 17, 2019 7:37 pm

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.2.4 on Windows 10
gerard24
Volunteer
 
Posts: 948
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: SUM specific cells based on *text* values

Postby RoryOF » Fri May 17, 2019 7:45 pm

Always good to have all aspects of a problem discussed.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29460
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Postby robleyd » Sat May 18, 2019 1:37 am

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   Expand viewCollapse view
=SUMIF($A$1:$A$5;"<=20";$B$1:$B$5)

sumif.png
sumif.png (5.91 KiB) Viewed 761 times
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2947
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SUM specific cells based on *text* values

Postby Doctor J » Sat May 18, 2019 9:49 pm

robleyd wrote:Try
Code: Select all   Expand viewCollapse view
=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   Expand viewCollapse view
=SUMIF(I3:17,"=S",G3:G7)
OpenOffice 5.2.7.2 Debian Stretch
Doctor J
 
Posts: 3
Joined: Fri May 17, 2019 1:02 pm

Re: SUM specific cells based on *text* values

Postby RoryOF » Sat May 18, 2019 9:58 pm

Leave out the = sign.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29460
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: SUM specific cells based on *text* values

Postby Villeroy » Sat May 18, 2019 11:00 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM specific cells based on *text* values

Postby Doctor J » Thu May 30, 2019 11:34 am

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
Doctor J
 
Posts: 3
Joined: Fri May 17, 2019 1:02 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 31 guests