# Master Excel’s 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.

## The Complete Course is Here!

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

## 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]=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. 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]). 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 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. 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!

### SUMPRODUCT Demo

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

### SUMPRODUCT Exercise

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

# Excel Dashboards that Inform & Impress

### Use these techniques in your own reports

#### Unbeatable value!

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!

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.

4. Farhan November 23, 2017 at 9:19 pm - Reply

Hi Leila, I every day watch one of your excel video everyday, you are the best, keep on good work.
Would it be possible to upload the spreadsheets you work on, so we can use it to practice together with you.

5. Braima December 26, 2017 at 2:14 pm - Reply

Hi Leila,

I recently discovered your videos on YouTube and have found them to be very useful. Your step by step explanation is very detailed. I tried the F10_Exercise on SUMPRODUCT Function but I couldn’t get the answer to Qu 5 : Count the number of products with prices higher than 6 and quantity lower than 2000 units. Any help would be greatly appreciated.

• Leila Gharani December 30, 2017 at 2:41 pm - Reply

Thank you! Glad you find them useful. The answer to this question is:
=SUMPRODUCT((E4:E29>L16)*(F4:F29

6. Jaric April 26, 2018 at 8:02 pm - Reply

• Leila Gharani April 26, 2018 at 8:22 pm - Reply

You’re very welcome.

7. Nitin Mehra May 11, 2018 at 7:06 am - Reply

Hi Leila,

Your tutorials and explanations are amazing. I really like the way you explain them. I have attempted SUMPRODUCT Exercise and following are my answers.

Q1. 474,174
Q2. 54,754
Q3. 13,503
Q4. 133,698
Q5. 2

Let me know if these are correct. Thanks again for putting so much hard work for people like us to learn. :)

• Leila Gharani May 23, 2018 at 5:43 pm - Reply

Hi Nitin, Yes – they are all correct. Well done! Not sure if you’re in my Advanced Excel course on Udemy. If yes, the answers are provided in the last tabs of the Exercise workbook.

8. Ghazi June 10, 2018 at 4:09 pm - Reply

• Leila Gharani June 10, 2018 at 4:32 pm - Reply

You’re very welcome Ghazi.

9. Julio August 19, 2018 at 5:42 pm - Reply

Thanks You´re Great, Excelent Page

• Leila Gharani August 21, 2018 at 2:41 pm - Reply

Thank you Julio. I’m glad you like it!

10. Tom October 1, 2018 at 4:26 pm - Reply

Hello Leila,

Thanks for the excellent videos and explanations. I am stuck on Q5 on F10_Exercise on SUMPRODUCT. What is the formula for that? Thanks again.

• Leila Gharani October 10, 2018 at 6:27 pm - Reply

Hi Tom, The formula is: =SUMPRODUCT((E4:E29>L16)*(F4:F29

11. Neo December 29, 2018 at 3:24 pm - Reply

I am trying to derive a sumproduct of two columns, providing multiple (not one) criteria are met.
I need to use the SUMPRODUCT formula for multiple criteria;
(1-Date from columns in two different sheets must be the same (column “C” -date- in sheet “Dresses” must match column “D”-date- in the second sheet on which I am typing the formula),
(2- Color of dress from the two different sheets must be the same (column “E” in the “Dresses” sheet must match column “E” on the sheet I am typing the formula,
(3–column “F” in the sheet I am working on must have colour “Red”.
On meeting the 3 criteria then calculate the sumproduct of columns N and S from the sheet “Dresses”.

Deriving the “SUMPRODUCTIFS” does not work out well as it would when one is simply looking for the sums as in formula below:
=IF((SUMIFS(‘[Dresses’!\$L\$3:\$L\$5000,'[Dresses’!\$E\$3:\$E\$5000,DQ\$3,'[Dresses’!\$O\$3:\$O\$5000,\$A\$7:\$A\$5000))=0,””, (SUMIFS(‘[Dresses’!\$L\$3:\$L\$5000,'[Dresses’!\$E\$3:\$E\$5000,DQ\$3,'[Dresses’!\$O\$3:\$O\$5000,\$A\$7:\$A\$5000)))

12. Kirk Bare January 9, 2019 at 9:48 pm - Reply

Hi, Enjoyed the video and I’m working on the solutions to F10 questions. But, the workbook I downloaded has values after the decimal in the Quantity column. Can you look into that please?

13. Syed January 22, 2019 at 1:40 pm - Reply

SUMPRODUCT can do these much Activities!!!!!! Great Explanation.
Thanks a lot Mam.

14. Syed January 22, 2019 at 3:27 pm - Reply

Hello Leila,
I have one doubt, Can SUBTOTAL be used along with SUMPRODUCT. Pls, Clarify mam. This can save most of my time. Pls, clarify.

15. Syed February 7, 2019 at 9:08 am - Reply

Hello Leila,
I have one doubt, Can SUBTOTAL be used along with SUMPRODUCT. Pls, Clarify mam. This can save most of my time. Pls, clarify

• Leila Gharani February 9, 2019 at 6:07 pm - Reply

You have to tweak it a bit. Check out this post from Exceljet.

Dear Leila hi!
I found your video very helpful. I used it (in a rather extensive file of 10000 rows and 40 columns) with combination of “index/match” function as follows: =SUMPRODUCT(((\$B\$4:\$B\$21=100)*(\$C\$4:\$C\$21=\$I\$1));INDEX(\$F\$4:\$F\$21;MATCH(J4;\$D\$4:\$D\$21;0))). I’m analyzing the formula:
(\$B\$4:\$B\$21=100) reffers to code nr of company.
(\$C\$4:\$C\$21=\$I\$1) reffers to year.
INDEX(\$F\$4:\$F\$21 reffers to references of values to be added (roomnights).
;MATCH(J4; reffers to lookup value of the code nr of debitor.
\$D\$4:\$D\$21; reffers to the lookup array with column with code numbers of debitors and
;0))) reffers to exact match.
I’m attaching a link for the relevant file: https://1drv.ms/x/s!AjYhLwLEcFn8hxUTm9ZH0Cpn6eeC
The result returns a”#value!” error. I have checked everything (or so i’m thinking) with “istext” formula but no success.
I would be very obliged if you could have a look to solve the problem?

• Bryon Smedley April 7, 2019 at 7:54 pm - Reply

Thanks – XelPlus team

17. Joran August 5, 2019 at 8:03 pm - Reply

Hey! For question 4 of your workbook you obviously wouldn’t want to combine the revenues when it’s the same business unit, e.g. when in L11 and L12 both BU_1 is selected -> with the standard formula it would be 2*54754=109507, while it should just be 54754.

Is there a simple way to fix this? For now I used:

=IF(L11=L12,SUMPRODUCT((B4:B29=L11)*E4:E29*F4:F29),SUMPRODUCT((B4:B29=L11)*E4:E29*F4:F29)+SUMPRODUCT((B4:B29=L12)*E4:E29*F4:F29))

But I was wondering if there is a more efficient way. Thanks in advance your tutorials and exercises are great!

18. Santosh KAMALA November 14, 2019 at 1:01 am - Reply

Hello Leila,

“Awesome” word will be less for your effort. Your step by step explanation is amazing. It’s pretty clear and understandable.

Best wishes,
Santosh Kamala

• Leila Gharani November 14, 2019 at 8:59 am - Reply

Thank you Santosh :)

19. Laza November 22, 2019 at 11:47 am - Reply

Hi Leila,

I have been enjoying and upgrading my Excel knowledge with your YouTube content. This is the first time I opened your website. Very, very useful to check out.

Cheers XL girl