Is there an alternative to SUMIF for arrays?

English
,
#1

The values in an array can be summed using:

=SUM({1,2,3})

But is there a way to exclude certain values from the sum if they are outside some limiting range? In other words, an equivalent to:

=SUMIF({1,2,3},">1")

Unfortunately the above doesn’t work since, unlike SUM, SUMIF supports cell ranges only, not cell ranges and arrays. (Well, technically SUMIF in Google Sheets and Zoho Sheets supports both, like SUM, but that’s not the case in LibreOffice Calc.)

I should clarify that the array {1,2,3} is actually generated by a fairly complex expression, something like:

IF(E21>$B$23:$B$28,$B$23:$B$28,E21-$B$22:$B$27)*$C$23:$C$28

So any practically useful solution should really avoid repeating the expression that generates the array since that would violate the DRY principal, making writing, reading and editing cell formulas much more error prone and difficult.

#3

Please file a bug report.

#4

SUMIF has 3 argument: a range where the criteria will be applied, the criteria, and the range you want to sum). If you have a list like {1,2,3,4,5} and you want to sum those element in the list >1, you must ise the list as the range for the riteria AND the range to sum: Try this:

=SUMIF({1,2,3,4,5}, “>1”, {1,2,3,4,5})

The answer should be 14.

#5

@JrgSampaio: who told you that the last optional argument is required in this case? Yes it works with the last argument - but the spec defines, that when the third argument is missing, the summation happens over the first one. This is definitely a bug that needs filing. SUMIF(S) shall either not allow inline arrays at all (with or without the last argument) - as ODF specifies (“Constraints: Does not accept constant values as the reference parameter”), or shall accept both variants (with or without the last argument), as an extension to the ODF spec.

#6

Sorry, but you answer is quite agressive. Nobody told me. In the function help (click on the little fx in the formula bar), the third argument appears as optional, but it is not clearly said that it would take the first argument if the third defaults. But note that in your previous answer you said clearly that SUMIF suppports cell ranges only, which seems to be incorrect. I see no reason why it shouln’t work with lists. Even so, I didn’t treat you the way you treat me. You should learn some etiquete. Since you are so angry with SUMIF, I suggest you to file a complaint yourself. But exercise some education before doing that. Wish you the best.

#7

I previously filed this bug report but it was closed as “notabug”, unfortunately. However, the reply given when it was closed makes it sound like the closer thought I was reporting a bug (despite my best efforts to make clear it was an enhancement request) so maybe there’s a chance it could be reopened at some point.

#8

Thank you both for your replies.

#9

For that, the “Importance” field must be “Enhancement” :wink:

#11

Ah, good tip, thanks. :slight_smile: And thanks for reopening the bug for further consideration.

#2

It works for me, but with all the arguments:
=SUMIF({1;20;3};">1";{1;20;3})
or
=SUMIFS({1;20;3};{1;20;3};">1")
I prefer to always use SUMIFS, because it is easier to add more criteria if necessary, having the range to sum at the beginning.

#10

Thank you for your reply. This solution has the problems that come with violating DRY though, as described in the last three paragraphs of my question. IMHO that makes it quite problematic (error prone, etc.) in real world usage.

The observation that arrays work in the SumRange argument, but not in the Range argument is fascinating though.

#12

I’ve just read your comment in bug 158788 about the potential of CURRENT(). Very interesting. Combining that suggestion with your suggestion above to use SUMIFS would resolve my DRY concerns by allowing for:

=SUMIFS({1;20;3};CURRENT();">1")

That’s pretty cool, thank you! :slight_smile:

(Which isn’t to say that bug 158789 shouldn’t be implemented, and that would make things even simpler (and more interoperable) of course.)

#13

Note that CURRENT should be avoided when possible. A really good would be implementation of LET.

#14

The Link behind »LET« is broken!

how to make an implementation unambigues?

import pandas as pd

somelist = [1,2,3,4,5]

#1 comprehension
sum_gt_2 = sum( x for x in somelist if x>2 )
print(f"{sum_gt_2 = }")

#2 filter_condition
filter_sum = sum(filter(lambda x: x>2, somelist))
print(  f"{filter_sum = }" )              

#3 DataFrame-Condition
pd_frame = pd.DataFrame(somelist)
pd_sum = pd_frame[pd_frame>2].sum()
print(f"{pd_sum[0] = }")
### prints:
sum_gt_2 = 12

filter_sum = 12

pd_sum[0] = 12.0

#1 cannot be done in Calc , #2 needs additional (FILTER, LAMBDA or LET)
#3 duplicates also the Argument »pd_frame«

#15

Thank you (and sorry for not checking) - fixed.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.