Master Excel’s SUMPRODUCT Formula

Excel SUMPRODUCT Formula

Are you currently using the SUMPRODUCT formula in your Excel files?

I wouldn’t be surprised if you say “No”.

Many know of it, but hesitate to use it. Or they don’t think it’s the right formula for the task at hand. Chances are it is. Knowing this function well, will simplify your spreadsheets and data analysis.

It is THE function to work smart in Excel. You will be able to answer questions faster. It’s a bit deceiving though, because it wears many hats.

Watch this video to get an idea of the power of SUMPRODUCT. This is just the tip of the iceberg. I am not exaggerating. I am just a big fan. Scroll down for the detailed steps and to download the workbook to practice along.

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.

Online Advanced Excel Course to become better

The Complete Course is Here!

You’d like to Improve your Excel Skills? Check out my Course: Advanced Excel Tips & Formulas to Work Smarter!

View Content

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.

Understanding Excel SUMPRODUCT function - simple form

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]=condition

There 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.

Excel SUMPRODUCT function using multiply sign

So that’s the result of Excel’s check. In this case, it is:

TRUE, TRUE, TRUE, FALSE, FALSE, TRUE

When 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, 1

Then it multiplies that with the Quantity column:

1*200 + 1*33 + 1*100 and so on

That’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]).

SUMPRODUCT formula using double negatives

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.

SUMPRODUCT function explained

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))…

SUMPRODUCT FORMULA with

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?

SUMPRODUCT in slow motion. How it turns true and false to 0 and 1.

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 on

In 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 on

That 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.

SUMPRODUCT complex calculations

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.

example of sumproduct function for free download

SUMPRODUCT Demo

You’d like to follow along? Trace the above steps described above and in the video with this demo file.

Free
free exercise for sumproduct to practice

SUMPRODUCT Exercise

Can you use the SUMPRODUCT formula on your own? Test your knowledge by completing the exercise.

Free

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

6 Comments

  1. Jeff August 24, 2017 at 5:58 pm - Reply

    Leila, Thank you for the files for download! You have my new favorite YouTube channel. Great job. Thank you very much.

    • Leila Gharani August 25, 2017 at 11:08 am - Reply

      You’re very welcome. I’m very happy to hear that Jeff :)

  2. Mahbub Hossain September 9, 2017 at 11:22 am - Reply

    Thanks Leila, this video is really helpful to understand the SUMPRODUCT function!

    I shall be waiting for more videos on your Youtube channel.

    Go on……… :-)

    • Leila Gharani September 10, 2017 at 4:58 pm - Reply

      Glad you like it Mahbub. Will make sure to keep it going :)

  3. angie September 19, 2017 at 3:50 am - Reply

    Can we do “and” argument in sumproduct like Company A and Company C?

    • Leila Gharani September 19, 2017 at 12:53 pm - Reply

      Hi Angie – Do you mean an “or” argument? Company A or company C? because “and” does not apply here – since we don’t have company A and C in one cell…. For the “or” argument, you would use the “+” sign instead of the “*” sign. This ensures that if Company is A or C, the results are added/included.

Leave A Comment

Share This