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.
The generic formula is:
=SUMPRODUCT( array1, array2, array3, …)
So how does a basic SUMPRODUCT formula work?
The input for this formula is 1 or several arrays. An array is nothing else then a selection of cells. The formula requires the user to define at least 1 and at most 255 arrays. 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:
The defined array is cells C6:C11 (note that I defined only 1 array in 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 are 4 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.
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. However, sometimes it’s not.
In example 1 the formula to multiply the components of ‘Array 1’ by ‘Array 2’ is:
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:
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.
=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 criteria and add up the components that meet these criteria. In other words, it can replicate a SUMIF formula.
Below example uses the formula:
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.
=SUMPRODUCT( (C4:C9) * (B4:B9 = I4) )
And that’s how SUMPRODUCT can replace a SUMIF formula. Yet there’s many more things you can do. For example, creating a multidimensional formula that adds up both rows and columns. Or combining multiple AND and OR conditions, or use the SUMPRODUCT formula instead of COUNTIFS. In the next post I focus on how to use SUMPRODUCT with Multiple Criteria for more advanced calculations.
I hope this clarifies the basics of the SUMPRODUCT formula. Please leave a comment below with questions or suggestions.