The Basics of SUMPRODUCT
SUMPRODUCT is an array function but is different than most of Excel’s array functions in that it doesn’t require Ctrl + Shift + Enter (CSE). SUMPRODUCT can be used to help solve many problems in non-obvious ways. It can be used like SUMIFS, it can be used to COUNT and it can also be used like INDEX and MATCH.
Featured Course
Black Belt Excel Package
SUMPRODUCT Explained in Easy Steps
The classical use of SUMPRODUCT is to sum the result of multiplications. Say for example you have Price and Quantity data as shown below.
To calculate for the Total Revenue, you’re going to multiply Price by Quantity and then add up the values in the Revenue column. You’d have a calculated column and a total for this calculated column which is Total Revenue.
With SUMPRODUCT on the other hand, you just have one formula that does it all for you!
You start by typing in =SUMPRODUCT( then you just highlight your Price column, add a comma to move on to the next argument, followed by the Quantity column and finally close the bracket). That’s it!
The good thing about SUMPRODUCT is that it can work with arrays and it doesn’t have to be entered as an array function so there’s no need to press CTRL + SHIFT + ENTER like for the FREQUENCY or TRANSPOSE functions.
What this formula does, is it takes the first cell, multiplies it by the second one. It does the same thing for the next row and adds the result of the first row to the second one and continues to do so for all the highlighted ranges. So it basically multiplies and then sums. This is why it’s important that your ranges have the same size.
Let’s take a more detailed look at this function. The way I wrote it in the example above is with a comma. By default the syntax is:
=SUMPRODUCT([array1],[array2],[array3],[array4]…)The way I generally write my SUMPRODUCT formulas is like this though:
=SUMPRODUCT([array1]*[array2])I use a * (multiplication operator) instead of a comma. I’m going to explain to you why by going through the next example.
The first example was a simple case. If you’ve ever used SUMPRODUCT you’ve probably used it in a form similar to the first example. With SUMPRODUCT, what some people don’t know is that you can also make exceptions.
Say for example, you’d like to get the sum of Quantity for only Product A. You can use the same formula. Because you’re making an exception where you’re only looking for products that are Product A, you have to highlight the Product column as the first array in this form:
=SUMPRODUCT([array1]=conditionThere is one thing you need to be careful with: the brackets! Whenever you use an equal sign in SUMPRODUCT make sure your argument is held separately inside brackets. Now let’s move on to the next argument. I want Quantity. All I have to do is highlight the Quantity column:
=SUMPRODUCT(([array1]=condition),[array2])Make sure the ranges are the same height. Press enter. You’ll see it doesn’t work. We get zero. Now let’s switch the comma into a *. The formula becomes:
=SUMPRODUCT(([array1]=condition)*[array2])Now it seems to work. Why?
Why Multiplication Operator or Double Negatives are Used for SUMPRODUCT
This is because the first argument [array1]=condition or [B6:B11]=I6 is checking if a particular entry is Product A. By checking I mean it labels each entry as TRUE or FALSE.
So that’s the result of Excel’s check. In this case, it is:
TRUE, TRUE, TRUE, FALSE, FALSE, TRUEWhen you leave the SUMPRODUCT with the comma it leaves the TRUE-FALSE’s as text and it can’t do anything with it. When you use the multiply sign and Excel multiplies a cell that’s Boolean i.e. TRUE or FALSE, it turns it into 1 for TRUE and 0 for FALSE. Basically the Product column becomes:
1, 1, 1, 0, 0, 1Then it multiplies that with the Quantity column:
1*200 + 1*33 + 1*100 and so onThat’s why I use the multiply sign. You can check if our result is correct by manually adding up Quantities for Product A. You will see that it yields 443 which is what our formula calculates.
If you do want to use the comma, you just need to perform some sort of numeric operation on the Boolean array. What some people do is to add two –‘s (minus operators) to the formula:
=SUMPRODUCT(- -([array1]=condition),[array2]).
You’re basically subtracting and then reversing it. You can also multiply it by 1:
=SUMPRODUCT(1*([array1]=condition),[array2]) As long as you do some type of mathematical operation on this, Excel is going to translate TRUE, FALSE to 1, 0.
Moving on to calculate Revenue: I do the same thing by using SUMPRODUCT with the Price and Quantity columns and putting in the exception for the Product. The formula becomes:
=SUMPRODUCT((B6:B11=I6)*C6:C11*D6:D11)Counter check by doing a manual addition of the Revenue column for Product A.
Some More Examples & Endorsement for SUMPRODUCT
What most people don’t know, is that SUMPRODUCT can do so much more than this. Because of its nature and the way it’s designed, it can do what VLOOKUP or INDEX MATCH can do. It can also do what SUMIFS can do and do it even better than SUMIFS can. Stay with me on this, because understanding this function is going to save you from doing a lot of in-between calculations and unnecessary workarounds in your existing Excel files.
Handling “OR”, “AND” Conditions with SUMPRODUCT
The good thing about SUMPRODUCT is that it can also do OR conditions and that’s something we can’t do with SUMIFS or COUNTIFS. With SUMIFS we can only check for one criteria per column. We could only check if Company is “Company A” but we can’t simply do an OR—i.e., “Company A” OR “Company B”. For each column, we are restricted to one condition. With SUMPRODUCT we’re not restricted, because we can easily use OR conditions within the formula.
Let’s see how you can do that. In this next example I will use the same data set, but the conditions for company are now: Company A or Company C. I also have a selection for Year and Month. I will start by using SUMPRODUCT to do a COUNT. The formula would be:
=SUMPRODUCT((([array1]=condition1)+([array1]=condition2))…
The AND is symbolized by a * (multiplication) sign, while an OR with a + (plus) sign. A plus sign is used in between the OR arguments for Company A or Company C. Make sure the entire OR argument is enclosed in brackets.
Before we continue with the formula, we can check if it is correct at this point. Once you hit ENTER you will see a number that tells you how many instances Company A or C are in your data set. In this case, it is 7 times. I’m not done with my argument because I still want to check for Year=2014 and Month= Jan. So right after the first OR statement, I’m going to add:
*([array2]=condition3)*([array3]=condition4))Remember whenever you have the equal sign, put that part in brackets. This combination should result in five instances.
Let me show you how SUMPRODUCT does the calculation in slow motion. The first thing it does, is to turn the result of the checks to a set of TRUE, FALSE values. See which cells are getting the TRUE here?
As a second step, because we’re multiplying, it turns the TRUE FALSE into 1 or 0 values. Then it multiplies them with one another. So basically it goes through the table (i.e. matrix) and does:
1*1*0=0 + 0*1*1=0 + 1*1*1=1 + and so onIn total you can see I have five instances where I get a 1. Add those and you get 5.
In the same manner you can calculate the sum for Quantity. To make things simple just copy the first formula (in edit mode) and paste it to the next row. All we have to do now is to bring in the Quantity column. The result is 470. Let’s double check the result in the above table. It does Company column multiplied by Year column, multiplied by Month column multiplied by the Quantity column. Basically it’s:
1*1*0*200=0 + 0*1*1*33=0 + 1*1*1*100=100 + and onThat gives us 470.
Now let’s move on to Revenue. We’ll have to multiply Quantity by Price (assuming we don’t have the Revenue column). Which means, all you have to do is bring in the Price column to the previous SUMPRODUCT formula.
As a last example, let’s calculate Average Price. Remember SUMPRODUCT is the sum of products. It is always adding something. How could I get the Average Price? This is done by getting the price and then dividing it by the result of the Count. Ensure bracket placement is correct.
Not enclosing arguments properly in brackets results in #VALUE! or even worse: a wrong number. Once you have the sum of Price, divide it by the Count. That’s going to be your average price.
Obviously these are dynamic, so if you change the conditions to Company B or C the results change as well.
Can you see why SUMPRODUCT is such an unusual and useful function? It’s not just the sum of products but you can use it as a COUNTIF function, a SUMIF function, and also a LOOKUP function.
What to Watch out For With SUMPRODUCT
Pay attention to your brackets though because their placement is really important. If you’re writing a SUMPRODUCT formula for the first time, double check your results and then trust it. If you ever forget about the syntax: what was the “+” for? Can I use the “,” or was it the “*”? Where should the brackets be and so one, come back to the Workbook to refresh your memory. There is no shame in that. I do it myself!
Test your Knowledge
Now it’s time for a formula challenge. Download the exercise book and try it out on your own. Share your feedback below.
SUMPRODUCT Demo
You’d like to follow along? Trace the above steps described above and in the video with this FREE demo file.
SUMPRODUCT Exercise
Can you use the SUMPRODUCT formula on your own? Test your knowledge by completing the exercise (FREE Download).
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
