Before dynamic arrays, SUMPRODUCT was the secret weapon of Excel experts, because it handles arrays without Ctrl+Shift+Enter. It still is useful in every version.
The basic idea
=SUMPRODUCT(B2:B10, C2:C10) ' qty × price for each row, then added upIt multiplies the ranges row by row and sums the products — total sales value in one formula, no helper column.
Counting and summing with conditions
=SUMPRODUCT((A2:A100="North")*(C2:C100>=10)) ' count rows
=SUMPRODUCT((A2:A100="North")*(C2:C100>=10), D2:D100) ' sum amountsEach comparison gives TRUE/FALSE; multiplying turns them into 1/0. It works like COUNTIFS/SUMIFS but also accepts calculations inside the conditions.
Things SUMIFS cannot do
| Need | SUMPRODUCT |
|---|---|
| Condition on a month of a date | =SUMPRODUCT((MONTH(B2:B100)=3)*D2:D100) |
| OR logic | =SUMPRODUCT(((A2:A100="North")+(A2:A100="East")>0)*D2:D100) |
| Case-sensitive count | =SUMPRODUCT(--EXACT(A2:A100,"ABC")) |
| Weighted average | =SUMPRODUCT(B2:B10,C2:C10)/SUM(C2:C10) |
| Count unique values | =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) (no blanks) |
⚠️ All ranges must be the same size, or you get #VALUE!. Avoid whole columns (A:A) — SUMPRODUCT calculates every row and becomes slow.
The double minus -- converts TRUE/FALSE to 1/0 when there is nothing to multiply by. Paste any SUMPRODUCT into the Formula Explainer to see its parts.