Search code examples
regexgoogle-sheetsfiltersumsumifs

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!

Solution

  • try:

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