SUMPRODUCT multiplies ranges row by row and adds the products. Because it handles arrays natively, it doubles as a flexible conditional sum and counter in every version.
Syntax
=SUMPRODUCT(array1, [array2], ...)
Argument
What it means
array1…
Ranges of the same size.
Examples
Example 1
=SUMPRODUCT(C2:C100, D2:D100)
Quantity × price for every row, added up.
Example 2
=SUMPRODUCT((MONTH(A2:A100)=3)*E2:E100)
March sales from a date column — something SUMIFS cannot do directly.
Power combo
=SUMPRODUCT(B2:B6, C2:C6)/SUM(C2:C6)
Weighted average: marks weighted by credits.
Common errors and fixes
You see
Why, and the fix
#VALUE!
Arrays of different sizes, or text in a multiplied range.
