Home »
ExcelBuiltInFunctions »
ExcelMathFunctions »
ExcelSumproductFunction
The Excel SUMPRODUCT Function
Function Description
The Excel Sumproduct function returns the sum of the products of the corresponding values
in a set of supplied arrays.
The syntax of the function is:
SUMPRODUCT( array1, [array2],
[array3], ... )
where the array arguments are one or more arrays
of numeric values that you want to sum the products of.
Note that:

All of the supplied arrays must have the same dimensions;

If just one array is supplied, the function returns the
sum of the values in that array.

Nonnumeric values in the supplied arrays are treated as the value zero;

In current versions of Excel (Excel 2007 and later), you can provide up to 255 arrays
of values to the Sumproduct function, but in Excel 2003, the function can only handle
up to 30 arrays of values;

Arrays that are supplied directly to the Sumproduct function should be surrounded
by curly braces, with values in the same row separated by a comma and each row
separated by a semicolon. An example of a vertical array is shown in cells C3 and C4
of the 'Formulas' spreadsheet below.
Sumproduct Function Examples
Formulas:

A 
B 
C 
1 
Array 1 
Array 2 
Sumproduct 
2 
1 
5 
=SUMPRODUCT( A2:A4, B2:B4 ) 
3 
2 
6 
=SUMPRODUCT( A2:A4, B2:B4, {2; 4; 1} ) 
4 
3 
4 
=SUMPRODUCT( A2:A4, B2:B4, {2; 4; "text"} ) 

Results:

A 
B 
C 
D 
1 
Array 1 
Array 2 
Sumproduct 

2 
1 
5 
29 
= 1x5 + 2x6 + 3x4 
3 
2 
6 
70 
= 1x5x2 + 2x6x4 + 3x4x1 
4 
3 
4 
58 
= 1x5x2 + 2x6x4 + 3x4x0
(note "text" is treated as 0)

5 




The above spreadsheets on the right
show 3 examples of the Sumproduct function.
Note that:
 The examples in cells C3 and C4 show how the array arguments
can be supplied directly, to the function, as well as in the form of arrays of cells;
 The "text" value in cell C4 is treated as the value 0 by the Sumproduct function.
Further details and examples of the Excel Sumproduct function are provided on the
Microsoft Office website
Sumproduct Function Error
If you get an error from the Excel Sumproduct Function, this is likely to be the #VALUE! error:
Common Error
#VALUE! 
 
Occurs if the supplied arrays have different dimensions.
