SUMPRODUCT is a multi-purpose formula. In essence, it multiplies arrays and returns the sum of those products. It is different from most Array formulas in Excel in that it doesn’t need CTRL + SHIFT + ENTER to work. SUMPRODUCT proves valuable in many scenario’s. You can use it for COUNT, as INDEX MATCH or replicate a SUMIF(S). And not only that, it’s actually a much more powerful formula with many uses. As it has so many uses, I will spend several posts on this formula.
You can find the recorded video above. If you prefer reading or need more instructions then continue reading.
Table of contents
How does SUMPRODUCT work?
The function has the following syntax:
=SUMPRODUCT( array1, array2, array3, …)
The formula requires the user to define at least 1 and at most 255 arrays.To give you an idea, an array is nothing else then a selection of cells. By default, the formula multiplies each of the components in the arrays. Next it returns the sum of each of those multiplications. This may still sound like magic to you now, but follow along and you will discover how the ‘magic’ works.
Using SUMPRODUCT as SUM Formula
Below example uses the formula:
=SUMPRODUCT( (C6:C11) )
The defined array is cells C6:C11. One could say that I defined a single array for this example. This is what happens
- First, the formula checks the given amount of arrays. In case of multiple arrays, the formula multiplies the components of each of the arrays.
- Second, the formula sums up the results of the previous action.
As this example contains a single array, no multiplication takes place. Summing up the results then amounts to ‘3792’. You could say that defining 1 array in a SUMPRODUCT formula, is equal to a SUM formula of the selected range. I do not recommend replacing all your SUM formulas with SUMPRODUCT. Yet for educational purposes this is good to know. In the next section the focus will be on multiplying several arrays within your formula.
An important skill to learn when using SUMPRODUCT is to add criteria. For that we need to be comfortable with multiplying arrays. Let’s therefore stand still by what it means to multiply two arrays.
Below you can find four examples of what happens when you multiply arrays. All examples have the same structure. You find two arrays, named ‘Array 1’ and ‘Array 2’. The SUMPRODUCT formula multiplies the components of these arrays with each other. And then returns the sum of each of those multiplications. Do note that the array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
For checking purposes I have added a manual method. The ‘Multiply Arrays’ column multiplies the values of ‘Array 1’ by ‘Array 2’. A basic SUM formula then sums up all the multiplied values. The manual method should be similar to the SUMPRODUCT method, but as you will find out sometimes the result is different.
In example 1 the formula to multiply the components of ‘Array 1’ by ‘Array 2’ is:
=SUMPRODUCT( B4:B9, C4:C9)
The calculation behind this is 283*0 + 539*0 + 921*0 + 722*0 + 833*0 + 494*0. The answer to that is ‘0’. In example 2 that would be 283*5 + 539*9 + 921*18 + 722*40 + 833*11 + 494*4 = ’62,863’. So far all works as expected.
Example 3 is a special case. The answer with the SUMPRODUCT formula, is different from the manual method. Why is that? SUMPRODUCT treats array entries that are not numeric as if they were zeros. When multiplying the numbers of ‘Array 1’ with the Boolean values of ‘Array 2’, the formula actually multiplies all the numbers by zero. The answer is ‘0’ for each of the multiplied components. SUMPRODUCT then sums up all the values, and gets to a total of zero.
So why does the manual SUM method work? When Excel performs a numerical operation with a Boolean value, it transforms TRUE to 1 and FALSE to 0. That’s why the manual multiplication in the ‘Multiply Arrays’ column results in a number. Yet SUMPRODUCT does not treat the multiplication of the Arrays in the same way.
The solution to make the formula work, is to do a numerical operation with ‘Array 2’ before multiplying it with ‘Array 1’. The following formula would give the right result:
=SUMPRODUCT( B15:B20, – – C15:C20)
In this formula I use a double negation as numerical operation. The values are first turned negative, and then turned positive again. This forces the Boolean values to transform to numbers with as result the situation in example 4. And SUMPRODUCT does handle that situation gracefully.
TIP: Up to now we have separated arrays with a list separator. An alternative is to write multiple arrays within the same array argument. You can also write the formula in example 3 as:
=SUMPRODUCT( (B15:B20) * (C15:C20) )
Notice that each array, now has its own place within parenthesis. Writing multiple arrays within a single array argument has an advantage. The main advantage is that Boolean values are automatically converted to numbers. So no chance of forgetting that. From now on, I will continue writing SUMPRODUCT formulas using this method.
If you would like to take a closer look at these examples, you can find them in the below file:
Using SUMPRODUCT as SUMIF Formula
The SUMPRODUCT formula allows you to add multiple conditions and add up the components that meet these conditions. In other words, it can replicate a SUMIF formula.
Below example uses the formula:
=SUMPRODUCT( (C7:C12) * (B7:B12 = “Berry”))
One thing to note is the brackets. When adding criteria to SUMPRODUCT, make sure the argument including the EQUAL sign is within brackets.
If we take the part “= Berry” away, the formula looks just like earlier examples. Adding “= Berry” to the array containing names, tests each component for being equal to ‘Berry’. For the SUMPRODUCT formula, the multiplication then looks like the table in Column E and F below.
When multiplying the Arrays as in above picture, the Boolean values transform to 1’s and 0’s, resulting in a total of ‘283’. Just like in example 3 of the previous section.
Tip: To make the formula dependent on a cell, replace the name by a cell reference:
=SUMPRODUCT( (C4:C9) * (B4:B9 = I4) )
And that’s how SUMPRODUCT can replace a SUMIF formula. Yet SUMPRODUCT is capable of much more. In the next post I focus on how to use SUMPRODUCT with Multiple AND Criteria for more advanced calculations. And if you can’t get enough of, the material continues with a series on how to combine AND and OR conditions. You will find it in the first article and second article.
I hope this clarifies the basics of the SUMPRODUCT formula. See you in the next post!