The Excel SUMPRODUCT function is a little known function. However it has a very fitting name because it multiplies and then returns the sum of the groups of multiplications, or creates a SUM of the PRODUCT.
Starting With The SUMPRODUCT Function
SUMPRODUCT(array1, [array2], [array3], …)
- array1 = the first array that you want to multiply and add together. This is required
- array2, array3, … = the next 2 to 255 arrays of numbers to multiply and add together. This is optional
A few notes about the function:
- If you have more than one array of numbers and they are not the same size, the function will return the #VALUE! error
- If any arrays do not have numbers, they will be treated as a zero by the function
- If a single array is passed to the function, it will only sum. It will not multiply.
How to Use It
In the image below, sample data and four examples of the SUMPRODUCT function are shown.
Notice that both cells A3 and A4 have a single array in their functions, while cells A5 and A6 have two arrays in theirs.
What do you think the result will be of each of the different functions?
The first function in cell A3 is using only two cells in the SUMPRODUCT function, specifically A1 and B1. So will it multiply the 1 (cell A1) * 2 (cell B1) and return 2, or will it add the two value and return 3?
What about the function in cell A4 that is using four cells in its SUMPRODUCT function? Will multiply the four cells or add them?
And then, what about the functions in cells A5 and A6? What will they return?
Here you can see what the results are.
Is it what you expected? If not, hopefully the explanation below will help you work through the formulas to see how the results were achieved.
Notice that when the SUMPRODUCT function is only passed one array of numbers, it does not multiply the numbers, but it adds them.
The function in cell A3 is passed the values in cell A1 of 1 and cell B1 of 2, so it is adding the 1 and 2 to equal 3.
The function in cell A4 is passed the values in cells A1 through A4 of 1,2,3 and 4. It is adding the four amounts to equal 10.
However, the functions in both cells A5 and A6 are both being passed two arrays, so the function is multiplying and then summing the product of the multiplication.
But how is the function returning the answers of 11 in cell A5 and 94 in cell A6?
Remember cell A5 has the formula of
=SUMPRODUCT(A1:B1,C1:D1).
You may think that it would multiply A1 * B1, multiply C1 * D1, and then add those two results together. Actually what the function is doing is multiplying A1 * C1, and B1 * D1, and then adding those two results together. This results in (1*3) + (2*4), which equals 11.
And what about the function in cell A6? Notice how it also refers to two arrays, the same as the formula in cell A5. The difference is that the arrays in cell A6 are not just referring to one row of data, but to two rows instead.
So the function in cell A6 returns 96, which is the result of (1*3) + (2*4) + (5*7) + (6*8).
To summarize how the function works, if there is more than one array being passed to it is this:
Take the first value in each array and multiply them. Then do the same for each next value in the arrays. Then add them together.
Now you fully understand how to use the SUMPRODUCT function.
But the really big question is, Where is this function useful?
What to Do With SUMPRODUCT
One SUMPRODUCT use can be for creating summaries of data, totalling based on criteria. For example, if you have data showing sales for different locations and colors and want a total of each location, or a total of each color, or a total of each unique combination of location and color, you can use SUMPRODUCT.
See the image below.
In the above image, notice that there are several entries for each location, each color, and also for the unique combination of location and color. For example, “North” is found on six rows in column A, and “Red” is found on seven rows in column B. The combination of “North” and “Red” on the same row is found on four rows.
Now, in typical Excel fashion, there are several ways that you could create those totals. You could make the data into a table, add totals to it, and then filter based on what you needed. You could use SUMIF or SUMIFS. And there are probably other ways that you do it. Let me know in the comments if you know of other ways to create summaries.
What will be considered here, is how to use SUMPRODUCT to create summaries of the data. Here is a summary of the above data based on location.
Here’s a summary of the data based on color.
And finally, here is a summary of the data based on a combination of location and color.
Each of these three summaries of the data are using the SUMPRODUCT function to filter the data and add the amounts based on the criteria.
Here are the same summaries, but showing the actual SUMPRODUCT function.
Did you notice how the SUMPRODUCT is a bit different. For example, one of the SUMPRODUCT function formulas is:
=SUMPRODUCT((E2:E26)*(A2:A26=H2)).
The cell H2 contains the word “North”. An alternate to the above formula could be
=SUMPRODUCT((E2:E26)*(A2:A26=”North”)).
So you can type in criteria, or use cell references as the criteria.
The first array is the column that you want to sum, the second array is the column that you want to filter on. This formula will filter on one criteria. To filter on more than one criteria, you just need to add more arrays and the filter for each one.
For example, the formula
=SUMPRODUCT((E2:E26)*(A2:A26=”North”)*(B2:B26=”Red”))
will add the amounts in column E that have “North” in column A and “Red” in column B.
Summary
SUMPRODUCT is not a commonly used function, but it can be used to multiple and add arrays of numbers. A probably surprising use of SUMPRODUCT is to create filtered subtotals of numbers.
If you have of other ways to use SUMPRODUCT let me know in the comments below.