The Excel SUMPRODUCT Function

Related Function:

SUMSQ

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.
Non-numeric 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.

Formulas:

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				= 1x5x2 + 2x6x4 + 3x4x0 (note "text" is treated as 0)

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

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.