SUM vs SUMPRODUCT: when the extra power is worth it
SUM adds up numbers. SUMPRODUCT multiplies corresponding values across ranges and THEN adds — which quietly makes it a Swiss-army knife for weighted totals and array-based conditional sums that plain SUM (and even SUMIFS) can't express. The trade-off is speed: SUMPRODUCT does array work, so it's heavier on big ranges.
The differences at a glance
| SUM | SUMPRODUCT | |
|---|---|---|
| Adds a list of numbers | Yes — its whole job, fast | Works (single range) but overkill |
| Multiply-then-sum (qty x price) | No — needs a helper column | SUMPRODUCT(qty, price) in one cell |
| Weighted average | No | SUMPRODUCT(values, weights)/SUM(weights) |
| Conditional sum with computed tests | No | SUMPRODUCT((MONTH(dates)=7)*amounts) etc. |
| OR conditions / overlap-safe | No | Yes, via array arithmetic |
| Speed on large ranges | Optimized, cheap | Array evaluation — noticeably heavier |
| Compatibility | Universal | Universal (both verified in every version we test) |
Which should you use?
- SUM — Straight totals of a column or a few cells — and reach for SUMIFS (not SUMPRODUCT) when you just need literal AND conditions. It's faster and clearer for the common case.
- SUMPRODUCT — Multiply-then-add in one step (order totals, weighted scores, hours x rate), weighted averages, or conditional sums whose criteria require computing on the data first (by month, by text length) or OR logic. It's the escape hatch when SUMIFS can't express the condition.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
SUMPRODUCT executes identically in every LibreOffice build and in our executed Google Sheets run — including its treatment of TRUE/FALSE arrays as 1/0 (whether via multiplication or a double-negative --). SUM has one executed divergence: a numeric string typed straight into the argument list, =SUM(1,"2",3), returns 6 in our executed Google Sheets run and per Excel's docs, but #VALUE! in LibreOffice. Desktop Excel behavior is per Microsoft's docs — we do not run desktop Excel. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 9 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
| Plain total | =SUM(A2:A100) |
| Order total (qty x price) | =SUMPRODUCT(B2:B100,C2:C100) |
| Weighted average | =SUMPRODUCT(A2:A10,B2:B10)/SUM(B2:B10) |
Full per-version details on each function page: SUM · SUMPRODUCT.