Using the Excel SUMPRODUCT Function
SummaryThe 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
Syntax=SUMPRODUCT (array1, [array2], [array3], ...)
Array1 is a required argument and the first array whose components you want to multiply and then add.
Array2, array3, and any subsequent arrays are optional. The function can handle up to 255 arrays.
Usage NotesThe 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).
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.
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:
From this, we can see that the count for S. Curry is 2 and the sum of his points is 53.
The function will return the #VALUE! error if the array arguments do not have the same dimensions.