The Excel SUMPRODUCT Function

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

Sumproduct Function Examples

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"} )
1Array 1Array 2Sumproduct 
21529= 1x5 + 2x6 + 3x4
32670= 1x5x2 + 2x6x4 + 3x4x1
43458= 1x5x2 + 2x6x4 + 3x4x0
  (note "text" is treated as 0)   

The above spreadsheets on the right show 3 examples of the Sumproduct function.

Note that:

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.