SUMIFS with OR criteria in Google Sheets

1
votes

Sample Table

I want to add values in Column C with the conditions that Column B = 0, AND Column A = "a" OR "b"

From what I've searched, this would work, but only in excel:

=SUM(SUMIFS(C:C,B:B,0,A:A,{"a","b"}))

I've tried adding "ArrayFormula" as this is a supposedly 'fix' for the above formula for Google Sheets (but tbf, I've only seen it used in COUNTIFS not SUMIFS)

=ArrayFormula(SUM(SUMIFS(C:C,B:B,0,A:A,{"a","b"})))

My expected result should be 4 but it keeps on returning 1. Weirdly, if I switch "a" and "b", it would return 3. As if it doesnt take the second criteria into account.

Ive been searching for days now, any help would be appreciated!

2
google-sheetssumifs

2 Answers

2
votes

try:

=SUM(FILTER(C:C, B:B=0, REGEXMATCH(A:A, "a|b")))
1
votes

@player0 has a top solution, but if you want to use sumifs(), then try:

=sumifs(C:C,B:B,0,A:A,"a")+sumifs(C:C,B:B,0,A:A,"b")

Or

=arrayformula(sumifs(C:C,B:B,0,regexreplace(A:A,"a|b","a"),"a"))

It can also be done with query():

=query({A:C},"select sum(Col3) where Col2=0 and Col1 matches 'a|b' label sum(Col3) '' ",0)