Home » ExcelBuiltInFunctions » ExcelMathFunctions » ExcelSumproductFunction
The Excel SUMPRODUCT Function
Basic Description
The Excel Sumproduct function returns the sum of the products of the corresponding values in a set of supplied arrays.
The format 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 information 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. 