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 scenarios. 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 the 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.
Multiplying Arrays
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 a 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.
The 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 Columns E and F below.
When multiplying the Arrays as in the 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) )
Conclusion
And that’s how SUMPRODUCT can replace a SUMIF formula. Yet SUMPRODUCT is capable of much more. In the next post, I will 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!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Just the sumproduct tutorial I needed, thankyou
BTW – did you mean criterion when you wrote criterium?
Bill. Thanks for opening my eyes, you’re absolutely right. I didn’t realise criterium was different than criterion. I got confused by the Dutch way of saying it (criterium). Great to see you around.
Rick, in the example under the head “Multiplying Arrays”, you have shown semi-colon between the ranges in the formula bar but in the formula at the foot of the table, you have shown comma. Is it okay?
Hi Sandeep,
Doh, you have a sharp eye! Depending on your computer’s settings you will use a semicolon or a comma as a separator. In my daily use I have a semi-column as separator. For the blog however I try to use a ‘comma’ as separator, as many computers have that as a standard. The picture and the formula bar are not consistent, thanks for spotting that. Please use the appropriate separator for your computer. The one that you always use when writing formulas.
Why double two pairs of parantheses in the first formula? A single pair will not work?
Hi Sandeep. And thanks for commenting. You’re right! In the example provided, a single parenthesis definitely works. So please feel free to use that. In the making of the screenshots I may have edited some more complex ones to a basic one and left the parenthesis.
/Rick