To add a condition to SUMPRODUCT, include the test as an extra array: =SUMPRODUCT(--(C7:C19=C2),E7:E19,F7:F19) multiplies units by weight per unit, but only for the rows where the product matches the value in C2. The condition returns TRUE/FALSE, the double minus converts those to 1s and 0s, and multiplying by zero cancels out every row that doesn’t qualify.
For two conditions, multiply them together — =SUMPRODUCT((C7:C19=C2)*(B7:B19=B2)*E7:E19*F7:F19) — which is AND logic. For OR logic, add them instead and cap at 1 so overlapping matches aren’t counted twice: =SUMPRODUCT(((C7:C19=C2)+(C7:C19=C3)>0)*E7:E19).
One thing to watch: every array must be the same size or you get #VALUE!, and text or blank cells inside a numeric range will also throw that error when multiplied — wrap them in N() or clean the data first.
SUMPRODUCT is one of the most important Excel functions. The best part about it is that you can modify it in several ways.
And one of its powers which I have discovered recently is using it as SUMPRODUCT IF. In short, a conditional SUMPRODUCT.
Well, as you know, you can use SUMPRODUCT to multiply and sum specified ranges or arrays. With a conditional SUMPRODUCT, you can multiply and sum only the values in those ranges that meet the criteria.
Yes, you heard it right. And you know the best part? There is no need to use the IF function for this. Isn’t it awesome?
Yes?
So today in this post, I’d like to share with you a problem and a simple way to use SUMPRODUCT IF to solve it.
So be with me to learn one of the most amazing Excel formulas and make sure to download the sample file.
How I Got to Know About This Conditional SUMPRODUCT
First things first.
Yesterday, I got a mail from one of my subscribers. She wanted to create a conditional statement with SUMPRODUCT and IF to get data from a table.
Here is the mail which I received:
Hey Puneet, I need your help. Is there any way to combine SUMPRODUCT IF? I have some data in a table, and I want to get the product of two columns meeting criteria. Please help me out.
Along with the mail, I got an Excel file from her with the following components. You can download this file from here to follow along.
- Stock Data: This table has the stock data of the products stored in different stores of her company. It has the product name, units in stock, and weight per unit.
- Drop-Down List: A drop-down menu to select a product.
From this table, she wanted to get the total quantity of a product (units * weight per unit) by selecting it from the drop-down. For example, if she selects “Pulses”, the cell should show the total quantity of pulses: the units multiplied by the weight per unit for each pulses row, added together.
Please note that the product name is the condition here.
Solution with SUMPRODUCT IF
At that time, I was sure about one thing: to get the sum of the product of arrays or ranges, we can use SUMPRODUCT.
But here, the game was to get the sum by multiplying the units and the weight per unit only for the rows that meet the criteria.
And here is the formula we can use (C2 has the drop-down, C7:C19 has the product names, E7:E19 the units, and F7:F19 the weight per unit):
=SUMPRODUCT(--(C7:C19=C2),E7:E19,F7:F19)
Now, when you select an entry from the drop-down list, this formula will only return the sum of the products for the rows that meet the criteria.
Hey, wait a minute: Just like this formula, I have listed a few more smart formulas that can amaze you.
How It Works
As you already know, SUMPRODUCT can work with arrays. So in the above method, we have used three arrays to get the sum of the products of the values.
The formula works in the following way.
1. Creating a Condition
First, we have an array to check the condition of the product name. It will check the values from the product column and return TRUE for the matched values and FALSE for the others. This check isn’t case-sensitive, so “pulses” and “Pulses” both match.
2. Using Double Minus Sign
Now, the next thing is to convert TRUE-FALSE values into 1-0 so that we can use them in the calculation. And for this, we have used the double minus sign before the first array.
Without it, SUMPRODUCT treats the TRUE and FALSE values as zeros (it treats every non-numeric entry as zero), and the formula returns 0. The double minus turns TRUE into 1 and FALSE into 0. Multiplying the condition by 1, or using the * operator between the arrays, does the same job.
3. Multiplying Arrays
After converting TRUE-FALSE into 1-0, the array will look something like this. Here, for all the values where the criteria are not met, we have zeros.
Remember, when anything is multiplied by zero, it returns zero. In this way, we get the product for only those rows where we have 1. In short, where the condition is met. In the end, SUMPRODUCT adds up these products and returns the total.
SUMPRODUCT with Multiple Conditions (AND Logic)
You can add more than one condition in the same way. Let’s say you also want the total quantity for a single store, and you have the store names in another column (say D7:D19) and the store to check in cell D2. Just add one more condition array with a double minus.
=SUMPRODUCT(--(C7:C19=C2),--(D7:D19=D2),E7:E19,F7:F19)
Here, a row gets 1 only when both conditions are TRUE, so the formula returns the total for that product in that store only. You can also multiply the conditions with each other, and it works the same way:
=SUMPRODUCT((C7:C19=C2)*(D7:D19=D2),E7:E19,F7:F19)
A condition doesn’t have to be a match. You can use any comparison operator, like this one that only includes the rows where the units are more than 5:
=SUMPRODUCT(--(C7:C19=C2),--(E7:E19>5),E7:E19,F7:F19)
SUMPRODUCT with OR Logic
To get the total for more than one product (say the products in C2 and C3), add the conditions with a plus sign instead of multiplying them. Then check if the result is greater than 0, so a row that matches either product gets 1 (and never 2).
=SUMPRODUCT(--((C7:C19=C2)+(C7:C19=C3)>0),E7:E19,F7:F19)
Partial Match and Case-Sensitive Conditions
Wildcards like * and ? don’t work in a SUMPRODUCT condition, as it compares the values exactly. If you want to match the product names that contain the text in C2 (like “Black Pulses” for “Pulses”), use ISNUMBER and SEARCH in the condition:
=SUMPRODUCT(--ISNUMBER(SEARCH(C2,C7:C19)),E7:E19,F7:F19)
And as the equal sign isn’t case-sensitive, use EXACT when the case matters. This one matches only “Pulses”, not “pulses”:
=SUMPRODUCT(--EXACT(C7:C19,C2),E7:E19,F7:F19)
Other Ways to Get the Same Result
If you use Excel 2021 or Microsoft 365, you can use FILTER to get the rows for the product and then SUM the products of units and weight:
=SUM(FILTER(E7:E19*F7:F19,C7:C19=C2,0))
In any version, you can also add a helper column that multiplies the units by the weight for each row. Enter the first formula below in G7 and drag it down to G19, and then use SUMIF on it (the second formula):
=E7*F7
=SUMIF(C7:C19,C2,G7:G19)
SUMPRODUCT is still the best choice when you want everything in a single cell without a helper column, and it works in all versions of Excel.
Common Errors and Mistakes
- #VALUE! error: The ranges are not the same size (for example, C7:C19 and F7:F18). All the arrays in SUMPRODUCT must have the same number of rows and columns.
- #VALUE! with the * operator: When you multiply the ranges with * and a cell has text (like “NA” in the units column), the formula returns #VALUE!. With commas between the arrays, SUMPRODUCT treats that text as 0 instead.
- The result is 0: You forgot the double minus before the condition, so the TRUE and FALSE values are treated as zeros.
- Slow workbook: You used full column references like C:C. Microsoft recommends not to use full columns with SUMPRODUCT, as it then multiplies more than a million rows. Refer to the exact range, or use an Excel table.
Conclusion
The best part about using this conditional SUMPRODUCT is that you don’t need to use IF, and all the calculation is in a single cell. As I said, SUMPRODUCT is one of the most powerful functions, and this is the best thing you can do with its powers.
Now tell me one thing.
Have you ever tried using a condition in SUMPRODUCT before?
Please share your views with me in the comment section. I’d love to hear from you, and please don’t forget to share it with your friends; I am sure they will appreciate it.
Frequently Asked Questions
What is the difference between SUMPRODUCT and SUMIFS?
SUMIFS can only add up the values of one range that meet the conditions. SUMPRODUCT can multiply two or more ranges first and then add up the products for the rows that meet the conditions, like units multiplied by weight. If you only need to sum one column, SUMIFS is simpler and faster.
=SUMIFS(E7:E19,C7:C19,C2)
Can I use SUMPRODUCT to count with a condition?
Yes. Use only the condition array, and SUMPRODUCT adds up the 1s, which gives you the number of rows that meet the condition.
=SUMPRODUCT(--(C7:C19=C2))
Do I need to press Ctrl + Shift + Enter for SUMPRODUCT?
No. SUMPRODUCT handles arrays on its own, so you just press Enter, even in older versions of Excel. That’s one reason it’s so popular for conditional calculations.
Does a conditional SUMPRODUCT work in Google Sheets?
Yes. The same formulas, with the double minus and the conditions, work in Google Sheets and in Excel for Mac and the web.
This post was incredibly helpful! I had been struggling to create conditional formulas in Excel, and using SUMPRODUCT IF really simplifies the process. The step-by-step examples made it easy to follow. Thanks for sharing!
I found your explanation helpful.
I have been using a similar formula and it worked a few months ago, but now it doesn’t work.
The older spreadsheets still work as they did before as long as I don’t bring in new data
The new files don’t.
I think something has changed within excel.
If you think you can help, let me know how to send you the files.
Fingers crossed
Hi Puneet,
This formula awesome!.
What if there is 1 more criteria need to be match(e.g. Cost Center with same products).
Much appreciate if you can advise this.
hi
can any one help to get the available qty of a part
table is like below
a1,a2,a3,a4 | available Qty1| available Qty2 | etc
Inv
A1 | 24
A2 | 30
A3 | 24
A4 | 15
Price so on so
Hi Puneet,
I want help with using sumproduct formula.
for eg
Item 100 piper has two serving (pegs) sizes (30 ml and 60 ml).
On a day sale is
30 Ml = 4 pegs = 120 ML
60 ml = 2 pegs = 120 ML
Total = 240 ML
What I want is how to apply the formula sumproduct so that
the sum comes in one place
Mohit, this is how I have used the example in my explanation. try to use the sample file that I have shared. let me know if you need further help. 🙂
I’m looking to use sumproduct to return a cumulative total or a zero for monthly totals year-to-date, January thru December, for months less than or equal to the current month or for months with no values which would be future months. I’m not seeing and examples in my search, see below:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
5 5 5 5 5 5 5 0 0 0 0 0
Desired results:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
5 10 15 20 25 30 35 0 0 0 0 0
All attempts using conditional operators — , etc have returne #Value! or other error while using the * returns:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
5 10 15 20 25 30 35 35 35 35 35 35
Do not want the existing cumulative total to be repeated in future months that have no zeros i.e. no monthly totals yet.
I would solve this different:
my excel is in german so i am not really sure about the right comands but i will try.
if(month(today())>Month(1 & A1); 5*Month(1 & A1); 0)
A1.value = Jan in your example
In the same example if I have multiple stores how to get total weight of the store which I will select from drop down.
it should be based on below conditions
Store
Name of the product
Product quantity
Product weight
Store Total Quantity
Abc 0
Store Packing Number Product Packing Size Unit Quantity Per Unit/Grams
abc R175338 Rosted Almonds Large 10 500
xyz R189624 Raw Peanuts Medium 25 300
rmp P188394 Popcorn Seeds Large 35 160
abc P144948 Pulses Large 28 450
xyz C110032 Cinnamon Powder Small 12 80
please help and reslove
Hey,
Thanks for this great explanation on this function. This is what I will probably need but I see SUMPRODUCT thinks with ‘and’ by default. Unfortunately, I need the ‘or’ version if feasible. The task is simple, I have a row of values (1,2,3,4,5) and a sheet full of values on the other side. I want to tell Sumproduct to count the number of rows in the table where any of my numbers occur. Each row should only be counted once regardless of how many matches found there. For this I will need to tell excel to “count rows in the range where any of the values from the 1-2-3-4-5 occur.” Would this be possible? I have searched so many forums and explanations on this function, no solution found. Each of them counts the number of cells (not the rows) or counts them only if multiple criteria are met at the same time. None of these fit my purpose. Thanks.
I have discovered this amazing function, but my Mac struggles with it. I would like to multiply array 1 by array 2. One of these is a lookup in a table based on array 2.
Is this possible?
The other problem is with gaps or blanks in arrays, do these disable the SUMPRODUCT function.
would this work on product() too? I am trying to calculate compounded returns and need to conditionally select daily data for each month and compound it to a single value.
share a snapshot of data.
Dear Sir,
Please provide me advance nested Index and matching formula. i have lot of doubt while using this.
Regards
Maheswar
Hi, Puneet, May I ask you how to filter a pivot table with “*”
say, I wish to filter a field with items start with “CS”
Thanks!
Puneet …. You are the best. …. A gem !!!
Thanks for your words.
What if i want to use the sumproduct formula to count zeros?? i’m making a form that needs to count days worked and days not worked, i have multiple criteria and it worked perfectly to sum the days worked…