The Excel SUMPRODUCT Function

Related Function:
SUMSQ

Basic 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:


Sumproduct Function Examples

 Formulas:
 ABC
1Array 1Array 2Sumproduct
215=SUMPRODUCT( A2:A4, B2:B4 )
326=SUMPRODUCT( A2:A4, B2:B4, {2; 4; 1} )
434=SUMPRODUCT( A2:A4, B2:B4, {2; 4; "text"} )
 Results:
 ABCD
1Array 1Array 2Sumproduct 
21529= 1x5 + 2x6 + 3x4
32670= 1x5x2 + 2x6x4 + 3x4x1
43458= 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:


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.