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

## 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!

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

**Excel Dashboards that Inform & Impress**

JeffAugust 24, 2017 at 5:58 pmLeila, Thank you for the files for download! You have my new favorite YouTube channel. Great job. Thank you very much.

Leila GharaniAugust 25, 2017 at 11:08 amYou’re very welcome. I’m very happy to hear that Jeff :)

Mahbub HossainSeptember 9, 2017 at 11:22 amThanks 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 GharaniSeptember 10, 2017 at 4:58 pmGlad you like it Mahbub. Will make sure to keep it going :)

angieSeptember 19, 2017 at 3:50 amCan we do “and” argument in sumproduct like Company A and Company C?

Leila GharaniSeptember 19, 2017 at 12:53 pmHi 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.

FarhanNovember 23, 2017 at 9:19 pmHi 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.

BraimaDecember 26, 2017 at 2:14 pmHi 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 GharaniDecember 30, 2017 at 2:41 pmThank you! Glad you find them useful. The answer to this question is:

=SUMPRODUCT((E4:E29>L16)*(F4:F29

JaricApril 26, 2018 at 8:02 pmThank you very much for your videos and files for download! You are amazing! :)

Leila GharaniApril 26, 2018 at 8:22 pmYou’re very welcome.

Nitin MehraMay 11, 2018 at 7:06 amHi 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 GharaniMay 23, 2018 at 5:43 pmHi 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.

GhaziJune 10, 2018 at 4:09 pmThank you very much for your helpful course and exercices

Leila GharaniJune 10, 2018 at 4:32 pmYou’re very welcome Ghazi.

JulioAugust 19, 2018 at 5:42 pmThanks You´re Great, Excelent Page

Leila GharaniAugust 21, 2018 at 2:41 pmThank you Julio. I’m glad you like it!

TomOctober 1, 2018 at 4:26 pmHello 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 GharaniOctober 10, 2018 at 6:27 pmHi Tom, The formula is: =SUMPRODUCT((E4:E29>L16)*(F4:F29

NeoDecember 29, 2018 at 3:24 pmI 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)))

Kirk BareJanuary 9, 2019 at 9:48 pmHi, 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?

SyedJanuary 22, 2019 at 1:40 pmSUMPRODUCT can do these much Activities!!!!!! Great Explanation.

Thanks a lot Mam.

SyedJanuary 22, 2019 at 3:27 pmHello Leila,

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

SyedFebruary 7, 2019 at 9:08 amHello Leila,

Your Explanations are too Great

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

Leila GharaniFebruary 9, 2019 at 6:07 pmYou have to tweak it a bit. Check out this post from Exceljet.

Andreas FeradourosMarch 16, 2019 at 11:22 amDear 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?

Thank you in advance for your time!

Bryon SmedleyApril 7, 2019 at 7:54 pmWe attempted to download your file, but there is an issue with your OneDrive link. Can you resubmit?

Thanks – XelPlus team