What it does
SUMPRODUCT multiplies arrays together and returns the sum of the products.
Syntax or pattern
=SUMPRODUCT(array1, [array2], ...)✅
5 practical examples
1
Calculate weighted revenue
Multiply quantity by price and total the result.
=SUMPRODUCT(Orders[Qty],Orders[Price])Avoids helper columns for simple row-by-row math.
2
Weighted average
Calculate weighted average score.
=SUMPRODUCT(Scores[Score],Scores[Weight])/SUM(Scores[Weight])A classic weighted average pattern.
3
Conditional total
Sum amounts where region is East.
=SUMPRODUCT((Sales[Region]="East")*Sales[Amount])TRUE/FALSE tests become 1/0 values.
4
Count rows with two conditions
Count rows matching product and region.
=SUMPRODUCT((Sales[Product]=H2)*(Sales[Region]=H3))A flexible alternative to COUNTIFS.
5
Total by month
Sum amounts for one month using date tests.
=SUMPRODUCT((TEXT(Sales[Date],"yyyymm")=TEXT(H1,"yyyymm"))*Sales[Amount])Useful when dates are daily but reports are monthly.
Common mistakes to avoid
- Selecting ranges with different sizes.
- Using text values where numbers are required.
- Forgetting how blanks and hidden rows are treated.
Related Excel examples
FAQ
Can this formula use Excel Tables?
Yes. Structured references often make summary formulas easier to read.