CODEWITHSUNDEEP
regexgoogle-sheetsfiltersumsumifs
Laine
Laine

Reputation: 75

SUMIFS with OR criteria in Google Sheets

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!

Upvotes: 6

Views: 14557

Answers (2)

Aresvik
Aresvik

Reputation: 4620

@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)

Upvotes: 8

player0
player0

Reputation: 1

try:

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

Upvotes: 8

Related Questions