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 (from executed tests)
Both execute identically in every version of Excel, Google Sheets, and LibreOffice we test — including SUMPRODUCT's treatment of TRUE/FALSE arrays as 1/0 (whether via multiplication or a double-negative --). The choice is capability and speed, not compatibility. For purely conditional sums, SUMIF/SUMIFS are the lighter tool; see SUMIF vs SUMPRODUCT.
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.