SUMIF vs SUMPRODUCT: conditional sums two ways
SUMIF (and SUMIFS) cover the common cases fast; SUMPRODUCT is the escape hatch when conditions get interesting — OR logic, calculations inside the condition, or weighting. Knowing which is which saves both headaches and recalculation time.
The differences at a glance
| SUMIF | SUMPRODUCT | |
|---|---|---|
| Simple condition ("East", ">100") | Perfect fit, very fast | Works but overkill |
| OR across conditions | Add two SUMIFs (double-counts overlap!) | Natural: ((a)+(b)>0)* — handles overlap correctly |
| Condition computed from the data (e.g. MONTH(dates)=7) | No — criteria are literal strings | Yes — any array expression |
| Multiply-then-sum (weights, qty x price) | No | Its original purpose |
| Wildcards ("Widg*") | Yes | No — use SEARCH/ISNUMBER instead |
| Speed on large ranges | Optimized, cheap | Array evaluation — heavier on full columns |
| Compatibility | Universal | Universal (documented across all versions; executed in every LibreOffice version we test) |
Which should you use?
- SUMIF — Single literal condition — and reach for SUMIFS (not SUMPRODUCT) when you just need several ANDed literal conditions.
- SUMPRODUCT — OR logic, conditions that require computing on the data first (dates by month, text lengths), or genuine sum-of-products like quantity x price.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
Both execute correctly in every LibreOffice version we test (desktop Excel behavior per docs — we do not run desktop Excel; Google Sheets is executed separately, see each function page) — choose on capability, not compatibility. One portability note: SUMPRODUCT treats TRUE/FALSE arrays as 1/0 when multiplied or double-negated (--), and that behavior is documented as identical across Excel, Sheets, and LibreOffice, matching what we see executed in LibreOffice. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 10 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| SUMIF (single condition) | =SUMIF(A2:A10,"East",C2:C10) |
| SUMPRODUCT with OR (East or West, no double-count) | =SUMPRODUCT(((A2:A10="East")+(A2:A10="West")>0)*C2:C10) |
| SUMPRODUCT quantity x price | =SUMPRODUCT(B2:B10,C2:C10) |
Full per-version details on each function page: SUMIF · SUMPRODUCT.