How to Use SUMPRODUCT in Excel
SUMPRODUCT multiplies corresponding items in two or more equal-sized ranges and sums the results. In financial models it computes weighted averages, blends prices with quantities into revenue, and performs conditional sums across several criteria without an array formula. It is one function that does the work of a helper column plus a SUM, which keeps models compact.
Syntax and how it works
The signature is =SUMPRODUCT(array1, [array2], ...). SUMPRODUCT lines up the arrays cell by cell, multiplies each set of aligned values, and adds the products into a single total. With one array it simply sums that array.
Every array must have the same dimensions, or SUMPRODUCT returns a #VALUE! error. The function's hidden power is that it handles arrays natively, so it can multiply ranges and evaluate logical conditions without being entered as a special array formula with Ctrl Shift Enter.
Worked example: weighted average cost of capital
You hold capital weights in B2:B4 and the cost of each source in C2:C4. A weighted average multiplies each weight by its cost and sums the results, which is exactly what SUMPRODUCT does in one step.
- Enter
=SUMPRODUCT(B2:B4, C2:C4). - SUMPRODUCT multiplies
B2*C2,B3*C3, andB4*C4, then adds the three products. - With the values shown, that is
0.5*0.08 + 0.3*0.05 + 0.2*0.12, which equals0.079. - If the weights do not sum to 1, divide by their total:
=SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4).
| Source | Weight (B) | Cost (C) |
|---|---|---|
| Equity | 0.5 | 0.08 |
| Debt | 0.3 | 0.05 |
| Pref | 0.2 | 0.12 |
0.5*0.08 + 0.3*0.05 + 0.2*0.12 = 0.079, a 7.9% blended cost.
Conditional sums with SUMPRODUCT
SUMPRODUCT can also sum with criteria by multiplying a value range against logical tests. A test like (B2:B200="West") returns an array of TRUE and FALSE values that Excel treats as 1 and 0 when multiplied, so only matching rows contribute.
This makes SUMPRODUCT a flexible alternative to SUMIFS when you need conditions that SUMIFS cannot express, such as criteria that depend on two columns multiplied together.
- Total revenue for the West region:
=SUMPRODUCT((B2:B200="West")*(D2:D200)). - Two conditions multiply together:
=SUMPRODUCT((B2:B200="West")*(C2:C200="Q2")*(D2:D200)). - Wrap each logical block in parentheses, since the multiplication turns each TRUE or FALSE into 1 or 0.
- Use the double-negative form
--(B2:B200="West")when you are not already multiplying by another array, to coerce the booleans to numbers.
Common pitfalls in models
Mismatched array sizes are the most common failure. Every range inside one SUMPRODUCT must span exactly the same number of rows and columns, or the formula returns #VALUE!.
Blank or text cells inside a value range also cause #VALUE! when multiplied, because text has no numeric value. The subtler risk is buried literals: a weight typed as 0.5 or a criterion like "West" written directly inside the formula is invisible to a reviewer and easy to forget when assumptions change. Move weights and criteria into labeled cells so the model stays auditable.
Find Hardcodes
Find Hardcodes catches literal weights and criteria buried inside a SUMPRODUCT so you can move them into proper assumption cells.
Get ModelMint See how it worksFAQ
What does SUMPRODUCT do in plain terms?
It multiplies aligned cells across two or more equal-sized ranges and adds up the products into one number. With a single range it just sums that range, and with logical tests it performs conditional sums.
Is SUMPRODUCT better than SUMIFS?
SUMIFS is faster and clearer for straightforward criteria. SUMPRODUCT wins when you need logic SUMIFS cannot express, such as multiplying two columns together or combining conditions that depend on a calculation, all without a helper column.
Why does my SUMPRODUCT return #VALUE!?
Usually the arrays are different sizes or one range contains text or blanks that cannot be multiplied. Confirm every range spans the same rows and columns, and that value ranges hold numbers rather than text.