Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

LibreOffice SUMIF function

Straight answers to factual questions
Forum rules
Direct questions and answers, this room is not for general discussion please
GrahamPlatt
Lemon Quarter
Posts: 2059
Joined: November 4th, 2016, 9:40 am
Has thanked: 1032 times
Been thanked: 823 times

LibreOffice SUMIF function

#476631

Postby GrahamPlatt » January 27th, 2022, 5:14 pm

Hi,

I wonder if anyone knows whether you can get SUMIF to accept a 'range' as its criteria?

=SUMIF(range,criteria,[sum range]) serves to compare two columns of values of equal length, and if the value in the first column meets the 'criteria', the value in the second column is added to the sum returned.

This is all very well if the criterion is simple e.g. ">0" (the criteria need enclosing in the quotation marks - as in string format. Help states that criteria are accepted as regular expressions). However, what I want is to use a 'criteria' expression to check whether the cell in 'range' is between two values - say between 3 and 6 inc. I've tried logical expressions e.g. =AND(>2,<7) (with and without quotes, or quotes just around the comparators (">2","<7") and =OR(=3,=4,=5,=6) (and tried semicolons not commas) etc, but no joy - getting Errors 509 or 510 which suggest there's a missing operand.

Is this just not possible with SUMIF?

Stompa
Lemon Slice
Posts: 825
Joined: November 4th, 2016, 6:29 pm
Has thanked: 151 times
Been thanked: 208 times

Re: LibreOffice SUMIF function

#476640

Postby Stompa » January 27th, 2022, 6:16 pm


GrahamPlatt
Lemon Quarter
Posts: 2059
Joined: November 4th, 2016, 9:40 am
Has thanked: 1032 times
Been thanked: 823 times

Re: LibreOffice SUMIF function

#476656

Postby GrahamPlatt » January 27th, 2022, 7:32 pm

Stompa wrote:I think you need SUMIFS()

https://help.libreoffice.org/6.4/en-US/ ... 6580665806


SUMIFS(Func_Range; Range1; Criterion1 [ ; Range2; Criterion2 [;...]]))

Thanks, but I've had a go and it seems that 'criteria' is subject to the same limitations as SUMIF and that adding the same range multiple times but with a different criteria seems to break it (i.e. it looks as though range1,criteria1; range2, criteria2; range3,criteria3 etc need to address different columns). Moreover if I duplicate the columns such that range1, criteria "=2" and create a duplicate column for range2 (containing the same data as range1) with criteria "=3", then it is only ANDs them - i.e. the cell in Func_range is only added when the cell in range1 =2 and that in range2 =3. I now see on the Microsoft page describing these functions that the results of logical operators which return TRUE and FALSE are evaluated as 1 and 0. So it doesn't look like I can use these.

modellingman
Lemon Slice
Posts: 614
Joined: November 4th, 2016, 3:46 pm
Has thanked: 594 times
Been thanked: 364 times

Re: LibreOffice SUMIF function

#476664

Postby modellingman » January 27th, 2022, 7:43 pm

Stompa wrote:I think you need SUMIFS()

https://help.libreoffice.org/6.4/en-US/ ... 6580665806


Its why SUMIFS() got added to Excel and its imitators, so multiple criteria can be used. It has cousins called COUNTIFS() and in Office 365, MAXIFS() and MINIFS().

Stompa
Lemon Slice
Posts: 825
Joined: November 4th, 2016, 6:29 pm
Has thanked: 151 times
Been thanked: 208 times

Re: LibreOffice SUMIF function

#476671

Postby Stompa » January 27th, 2022, 8:01 pm

GrahamPlatt wrote:
Stompa wrote:I think you need SUMIFS()

https://help.libreoffice.org/6.4/en-US/ ... 6580665806


SUMIFS(Func_Range; Range1; Criterion1 [ ; Range2; Criterion2 [;...]]))

Thanks, but I've had a go and it seems that 'criteria' is subject to the same limitations as SUMIF and that adding the same range multiple times but with a different criteria seems to break it (i.e. it looks as though range1,criteria1; range2, criteria2; range3,criteria3 etc need to address different columns). Moreover if I duplicate the columns such that range1, criteria "=2" and create a duplicate column for range2 (containing the same data as range1) with criteria "=3", then it is only ANDs them - i.e. the cell in Func_range is only added when the cell in range1 =2 and that in range2 =3. I now see on the Microsoft page describing these functions that the results of logical operators which return TRUE and FALSE are evaluated as 1 and 0. So it doesn't look like I can use these.


One of the examples in that link I gave was:

=SUMIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6))

so I imagine you should be able to do:

=SUMIFS(C2:C6;B2:B6;">2";B2:B6;"<7")

does that not work?

GrahamPlatt
Lemon Quarter
Posts: 2059
Joined: November 4th, 2016, 9:40 am
Has thanked: 1032 times
Been thanked: 823 times

Re: LibreOffice SUMIF function

#476683

Postby GrahamPlatt » January 27th, 2022, 8:32 pm

Stompa wrote:
One of the examples in that link I gave was:

=SUMIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6))

so I imagine you should be able to do:

=SUMIFS(C2:C6;B2:B6;">2";B2:B6;"<7")

does that not work?


It does! Don't know where I was going wrong. Thanks a bunch Stompa.

Edit: Of course; I see now. Said it myself - it ANDs them. I was thinking that if I did that I'd get everything over 2 and everything below 7 - i.e. everything, and the things between 2 and 7 twice!

Stompa
Lemon Slice
Posts: 825
Joined: November 4th, 2016, 6:29 pm
Has thanked: 151 times
Been thanked: 208 times

Re: LibreOffice SUMIF function

#476717

Postby Stompa » January 27th, 2022, 10:38 pm

GrahamPlatt wrote:
Stompa wrote:
One of the examples in that link I gave was:

=SUMIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6))

so I imagine you should be able to do:

=SUMIFS(C2:C6;B2:B6;">2";B2:B6;"<7")

does that not work?


It does! Don't know where I was going wrong. Thanks a bunch Stompa.

Edit: Of course; I see now. Said it myself - it ANDs them. I was thinking that if I did that I'd get everything over 2 and everything below 7 - i.e. everything, and the things between 2 and 7 twice!


I'm glad it worked. Just out of interest, you could have actually done it with SUMIF() though it would have been a bit more convoluted and I guess less efficient. Just sum the whole lot, then subtract those <=2 and those >=7. So

=SUM(C2:C6)-SUMIF(B2:B6;"<=2";C2:C6)-SUMIF(B2:B6;">=7";C2:C6)

GrahamPlatt
Lemon Quarter
Posts: 2059
Joined: November 4th, 2016, 9:40 am
Has thanked: 1032 times
Been thanked: 823 times

Re: LibreOffice SUMIF function

#476757

Postby GrahamPlatt » January 28th, 2022, 7:21 am

Stompa wrote:=SUM(C2:C6)-SUMIF(B2:B6;"<=2";C2:C6)-SUMIF(B2:B6;">=7";C2:C6)



Yes, and now I’ve got my head around it,
=SUMIF(B2:B6;"<=7”;C2:C6)-SUMIF(B2:B6;">=2”;C2:C6)
But SUMIFS is better.
It’d be a lot better still if they’d make “criteria” accept logical expressions though.


Return to “Does anyone know?”

Who is online

Users browsing this forum: No registered users and 11 guests