Excel SUMPRODUCT Function

Using the Excel SUMPRODUCT Function


The SUMPRODUCT function in Excel is used to multiply components in a range or array and return the sum of those products. SUMPRODUCT is very useful and provides the ability to count and sum with more flexibility than COUNTIFS or SUMIFS. Other functions can be used within SUMPRODUCT to extend its abilities.

If we take the example at the top of this page, SUMPRODUCT is performing the following operations:

(3*2) + (4*7) + (8*6) + (6*7) = 124


=SUMPRODUCT (array1, [array2], [array3], ...)

Syntax Breakdown

Array1 is a required argument and the first array whose components you want to multiply and then add.

Array2, Array3
Array2, array3, and any subsequent arrays are optional. The function can handle up to 255 arrays.

Usage Notes

The SUMPRODUCT function takes in array or range arguments and multiplies the components and then adds them together. The function is much more versatile than COUNTIFS or SUMIFS. If only one array is supplied the function will simply sum the items in the array.

The function treats non-numeric array entries as if they were zeros (0).

Below we examine a few common use cases for SUMPRODUCT.

SUMPRODUCT Conditional Sums and Counts
Let's assume that we have some data for some basketball players on points per game. Using SUMPRODUCT we are able to count and sum the total points for specific players (S. Curry in this example).

Excel SUMPRODUCT Example

You may be wondering why the double negative sign (--) was used in the function. This is a trick, often used in advanced formulas, to force TRUE and FALSE to becomes 1's and 0's. An example follows of how this works for the sum example above.

SUMPRODUCT Double Negative Example

Each array has seven items. Array1 contains TRUE and FALSE values. This is from the expression A9:A15 = "S. Curry". Array2 contains the values in B9:B15. SUMPRODUCT will multiply the items in the first array by the corresponding items in array2. The problem with the current situation is that the output will be zero (0) because TRUE and FALSE are treated as zeros (0). Thus, we need to force the items in array1 to be numeric. This is why the double-negative is used; it forces TRUE and FALSE to become 1's and 0's. We then have the following:

SUMPRODUCT Double Negative Example II

From this, we can see that the count for S. Curry is 2 and the sum of his points is 53.

#VALUE! Error
The function will return the #VALUE! error if the array arguments do not have the same dimensions.