How to SUMIF with OR criteria (this value or that one)

✓ Verified in LibreOffice 25.8.7.3 Google Sheets returned something else (2026-08-30)

Sum rows matching ANY of several values — East or West, two product codes, multiple statuses.

The formula

App	Formula	Notes
Excel (desktop)	`=SUM(SUMIF(A2:A6,{"East","West"},B2:B6))`	The array constant makes SUMIF return one subtotal per value; SUM adds them.
Google Sheets	`=SUMPRODUCT((A2:A6="East")+(A2:A6="West"),B2:B6)`	Executed 2026-08-30: =SUM(SUMIF(A2:A6,{"East","West"},B2:B6)) returned 110 in Google Sheets against 160 in LibreOffice — 110 is the East subtotal alone, because Google Sheets does no implicit array evaluation inside a scalar function’s arguments, so only the first value of the array constant reaches SUMIF. SUMPRODUCT with added boolean tests gets the whole OR in one pass; =ARRAYFORMULA(SUM(SUMIF(A2:A6,{"East","West"},B2:B6))) is the documented wrap of the original. Both are documented Sheets syntax, carrying no executed result of their own.
LibreOffice Calc	`=SUM(SUMIF(A2:A6,{"East","West"},B2:B6))`	Identical.

How it works

SUMIF against the array constant {"East","West"} evaluates once per value — producing {110, 50} — and the outer SUM collapses them: 160. That is Excel and LibreOffice; Google Sheets returned 110 when we executed it, the East subtotal alone, so use the SUMPRODUCT form (or an ARRAYFORMULA wrap) there. This is safe for OR-of-EXACT-values because one row can't match two different values, so nothing double-counts (unlike adding overlapping condition SUMIFs). For OR across different COLUMNS' conditions, where overlap is possible, use the SUMPRODUCT pattern from the sumif-vs-sumproduct comparison instead.

The Google Sheets alternative

Google Sheets needs different syntax for this task. The formula below is Sheets-specific — it was executed in Google Sheets (2026-08-30) against the same sample data as the worked example, and the value beside it is what Google returned. The LibreOffice column reads n/a because the formula is outside LibreOffice’s dialect, so there is nothing of ours to report there.

Formula	What it does	Returned by LibreOffice 25.8.7.3	Returned by Google Sheets (executed 2026-08-30)
`=SUMPRODUCT((A2:A6="East")+(A2:A6="West"),B2:B6)`	SUMPRODUCT with added boolean tests does the whole OR in one pass	n/a (Sheets-only formula)	160 Google Sheets alternative (executed 2026-08-30)

Verified, not just documented

We ran =SUM(SUMIF(A2:A6,{"East","West"},B2:B6)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 160 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. For the worked example Google Sheets returned 110, which is not what LibreOffice returned (160) — both values are shown as each engine produced them, and the disagreement itself is the finding. A further 1 row is a Google Sheets alternative: Sheets-specific syntax, executed in Google Sheets only, so the LibreOffice column reads n/a for it. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

SUM · SUMIF · SUMPRODUCT — see full Excel, Google Sheets & LibreOffice compatibility for each.