SUMPRODUCT function
SUMPRODUCT multiplies lists item by item and adds up the results. It is also a flexible way to count or sum with multiple conditions. The lists must match in size.
Example
Read it as a sentence: multiply each value in AB1:AB3 by the value in the same row of AC1:AC3, then add the products, which gives 32.
Excel shows 32
AB
AC
1
1
4
2
2
5
3
3
6
Data
Syntax
SUMPRODUCT(array1, [array2], ...)
Arguments in [square brackets] are optional.
-
array1(required) - The first array or range.
-
[array2], ...(optional) - More arrays of the same size.
Returns: The sum of the products of matching positions.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SUMPRODUCT({1,2},{3,4}) | 11 | Arrays |
=SUMPRODUCT(5) | 5 | Scalar |
=SUMPRODUCT("5") | 0 | Text scalar |
=SUMPRODUCT(Data!AB1:AB3,Data!AC1:AC3) | 32 | Two ranges |
=SUMPRODUCT(Data!AB1:AB3) | 6 | Single range |
=SUMPRODUCT(Data!AD1:AD3,Data!AB1:AB3) | 10 | Text in range treated as 0 |
=SUMPRODUCT({1,2,3},{1;2;3}) | #VALUE! | Row vs column |
=SUMPRODUCT(Data!AB1:AB3,Data!AC1:AC2) | #VALUE! | Size mismatch |
The Data sheet these formulas read
A cell that starts with = holds that formula.
AB
AC
AD
1
1
4
1
2
2
5
x
3
3
6
3
Data
Watch out for
- Arrays must match in size or you get #VALUE!.
- Non-numbers count as zero.
Where it works
- Excel
- SUMPRODUCT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .