1

How to use Google Spreadsheet SUMIF agregation function with more than one condition?

What I currently have is formula: =SUMIF( A1:A50, "match_this",B1:B50)

But I want to improve it to have 2 conditions: =SUMIF( A1:A50, _ONE_OF_("match_this", "match_that"),B1:B50)

Is there operand like my imaginary _ONE_OF_ or what is the most simple, and correct way to handle this?

0

1 Answer 1

3

One way to achieve this is by combining sum with filter. The filter can use several conditions, which may be joined by + (meaning OR), or by * (meaning AND). Example:

=sum(filter(B1:B50, (A1:A50 = "match_this") + (A1:A50 = "match_that")))

sums the entries in B1:B50 where A1:A50 is either of two things.

Notes

  • There is sumifs but it joins conditions only as AND
  • There is or but it's not supported by sumif
  • The great flexibility of filter is that it focuses on one thing: filtering a range by a condition. You can apply any function you want to the filtered results: sum, product, count, maximum, etc. Also, the condition can be expressed by any formula, not constrained by a rigid syntax of sumif and its relatives. For example, advanced string filtering can be done with regexmatch.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.