Excel SUMPRODUCT: Conditional Totals
By Szabó Gergő · Updated
SUMPRODUCT multiplies arrays element by element and adds the result. Boolean tests coerce to 1 and 0.
Syntax and arguments
=SUMPRODUCT(array1, [array2], ...)- array1
- A range or expression of the same size as the others.
SUMPRODUCT examples
01
Two criteria total
Region B, status C, amount D.
=SUMPRODUCT((B2:B100="East")*(C2:C100="Paid")*(D2:D100))TRUE becomes 1. Only East+Paid amounts survive.
02
Weighted average
Score B, weight C.
=SUMPRODUCT(B2:B20,C2:C20)/SUM(C2:C20)The classic weighted mean.
03
OR criteria
East or West.
=SUMPRODUCT(((B2:B100="East")+(B2:B100="West"))*(D2:D100))+ is OR. * is AND.
Common mistakes
Ranges of different sizes
Every array must be the same height and width or you get #VALUE!.
Using AND() on ranges
AND returns one value. Use * between tests instead.
Whole-column SUMPRODUCT
It is slow. Limit the range.
SUMPRODUCT FAQ
SUMIFS vs SUMPRODUCT?
SUMIFS is faster and clearer for simple AND criteria. SUMPRODUCT can do OR and array math.
Does it work in Excel 2010?
Yes. That is why it still appears in older models.
Can it count?
=SUMPRODUCT((B2:B100="East")*(C2:C100="Paid")) counts matching rows.