SUMPRODUCT in Excel: Multiply and Add in One Step
Use SUMPRODUCT to total price × quantity without a helper column, calculate weighted averages, and count or sum with several conditions in any Excel version.
By Ayoub Farid · · 1 min read
SUMPRODUCT multiplies matching items in two or more ranges and adds the results. In plain words: total revenue from prices and quantities in one formula, without a revenue column.
Syntax
=SUMPRODUCT(array1, [array2], ...)Total revenue
| A | B | C | |
|---|---|---|---|
| 1 | Item | Price | Qty |
| 2 | Espresso | 2.50 | 34 |
| 3 | Latte | 3.50 | 41 |
| 4 | Tea | 2.00 | 18 |
=SUMPRODUCT(B2:B4,C2:C4)= 2.50×34 + 3.50×41 + 2.00×18 = 85 + 143.50 + 36 = 264.50
Weighted average
Average price weighted by quantity:
=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)See weighted average.
Conditions (works in every version)
Revenue from Coffee only (category in D):
=SUMPRODUCT((D2:D50="Coffee")*B2:B50*C2:C50)The condition becomes 1 or 0, so non-Coffee rows multiply to 0.
Count rows that meet two conditions:
=SUMPRODUCT((D2:D50="Coffee")*(C2:C50>20))Rules
- All arrays must be the same size, or you get
#VALUE!. - Text in multiplied ranges becomes an error when you use
*. Clean the data first. - For simple conditional sums, SUMIFS is easier to read.
Frequently asked questions
What does SUMPRODUCT do?
Multiplies corresponding values in ranges and adds up the products.
When should I use SUMPRODUCT instead of SUMIFS?
When you need to multiply columns, use OR logic, or test conditions SUMIFS can't (like parts of dates).
Why does SUMPRODUCT return #VALUE!?
The ranges are different sizes.
Does SUMPRODUCT work in Google Sheets?
Yes.