Sometimes we need to make small amendments to formulas to make them effective.
Just like that, SUMIF OR. It’s an advanced formula that helps you to increase the flexibility of SUMIF. Yes, you can also do SUMIFS as well. But before we use it let me tell you one thing about SUMIF.
In SUMIF, you can only use one criterion and in SUMIFS, you can use more than one criterion to get a sum.
Just a thing like this. Let’s say, in SUMIFS, if you specify two different criteria, it will sum only those cells which meet both of the criteria.
Because it works with AND logic, all the criteria should meet to get a cell included. When you combine OR logic with SUMIF/SUMIFS you can sum values using two different criteria at the same time.
Without any further ado let’s learn this amazing formula.
Do We Really Need OR Logic? Any Example?
Let me show you an example where we need to add an OR logic in SUMIFS or SUMIF.
Take a look at below data table where you have a list of products with their quantity and the current status of the shipment.
In this data, you have two types of products (Faulty and Damaged) that you need to return to our vendor.
But before that, you want to calculate the total quantity of both types of products. If you go with the normal method, you have to apply SUMIF twice to get this total.
But if you use an OR condition in SUMIF then you can get the total of both of the products with just one formula.
OK, Let’s Apply OR with SUMIFS
Before we start, please download this sample file from here to follow along.
In any other cell in your worksheet where you want to calculate the total, insert the below formula and hit enter.
=SUM(SUMIFS(C2:C21,B2:B21,{"Damage","Faulty"}))
In the above formula, you have used SUMIFS but if you want to use SUMIF you can insert the below formula in the cell.
=SUM(SUMIF(B2:B21,{"Damage","Faulty"},C2:C21))By using both of the above formulas you will get 540 in the result. To cross-verify, just check the total manually.
How this formula Works
As I said, the SUMIFS function uses AND logic to sum values. But, the formula which you have used above includes an OR logic in it. To understand this formula you have to split it into three different parts.
- The first thing is to understand that, you have used two different criteria in this formula by using the array concept. Learn more about the array from here.
- The second thing is when you specify two different values using an array, SUMIFS has to look for both of the values separately.
- The third thing is even after using an array formula, SUMIFS is not able to return the sum of both of the values in a single cell. So, that’s why you have to enclose it with the SUM function.
With the above concept, you are able to get a total for both types of products in a single cell.
It will work the same with SUMIF and SUMIFS.
And, you can also further expand your formula by specifying the product name in the second criterion in SUMIFS to get the total for a single product.
Get the Excel File
Using Dynamic Criteria Range
In the comment section, Shay asked me about using a cell reference to add multiple criteria instead of inserting them directly into the formula.
Well, that’s a super valid question and you can do this by using a dynamic named range instead of hardcore values.
And for this, you need to do just two little amendments to your formula.
- The first is that instead of using curly brackets you need to use a named range (the best way is to use a table) of your value.
- And after that, you need to enter this formula by using Ctrl + Shift + Enter as a proper array formula.
So, now your formula will be:
Multiple Criteria with Different Columns
Let me take you to the next level of this formula. Just look at the below data table.
In this table, you have two different columns with two different kinds of statuses. Now, from this data table, you need to sum the quantity where the product is damaged return status is “No”.
That means only those Damage and Faulty products which are not been returned yet. And, the formula for this will be.
=SUM(SUMIFS(D2:D21,B2:B21,{"Damage","Faulty"},C2:C21,"No"))
Get the Excel File
Conclusion
The best part about using the OR technique is you can add as many criteria to it. If you want to use only OR logic then it’s fine to use SUMIF. And if you want to create an OR condition and AND condition in a single formula then you can use SUMIFS for that.
You know, this is one of my favorite formula tips and I hope you found it useful.
Related Formulas
- Sum Greater than Values using SUMIF
- Sum Not Equal Values (SUMIFS) in Excel
- SUMIF with Wildcard Characters in Excel
- SUMIFS Date Range (Sum Values Between Two Dates Array)
- Combine VLOOKUP with SUMIF
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Values Based on Year (SUMIF Year)
- SUMIF By Date (Sum Values Based on a Date)
- Back to the List of Excel Formulas
hello
if I want to do the opposite logic the results become double counted as it seems each “not equal to” is count separetely..
if I want to exclude all products that are not faulty and not damaged
exe: SUM(SUMIFS(C2:C21,B2:B21,{“Damage”,”Faulty”}))
the results will count all not damaged first (including faulty ones ) and then count the not faulty (including the damaged ones) so having almost double amounts…
how can it be solved?
i want to show my sale data date and customer wise amount
Is there a way for the {“Damage”,”Faulty”} to be a single cell reference from the below formula?
=SUM(SUMIFS(C2:C21,B2:B21,{“Damage”,”Faulty”}))
I saw the mentioning of “named_range” but that selects two cells that contains Damage and Faulty. I was hoping there is a way to put two or more criteria in one cell.
I tried putting {“Damage”,”Faulty”} in a cell (lets say D5) and with the formula:
=SUM(SUMIFS(C2:C21,B2:B21,D5))
=SUM(SUMIFS(C2:C21,B2:B21,indirect(“D5”)))
and some other combinations but have not found the way to do this..
Thank you
=SUM(SUMIFS(J2:J2646,F2:F2646,{“RUH-G1″,”RUH-G2”},G2:G2646,{“RUH-G1″,”RUH-G2”},H2:H2646,{“RTO”}))
I am getting wrong result with this formula. Tell me what mistake am I doing
=SUM(SUMIFS(C2:C21,B2:B21,{“Damage”,”Faulty”}))
Google sheet and Excel will give different answers. Google sheet will consider only what is 1st written in curly braces i.e. Damage.
same here. please suggest a solution for google sheet as well.
unfortunately, wrong answer, because it will sum duplicates. kindly check again.
Can you help me to figure out the right formula for summing Faulty or Damaged only for Product A or B.
It seems if I add both criteria the way you done it does sum the Damaged A and Faulty B only.
=SUM(SUMIFS(D2:D21,B2:B21,{“Damage”,”Faulty”},A2:A21,{“Product-A”,”Product-B”}))
Dear Puneet,
I am a Sourcing Manager at a pharma company. You are AMAZING. I read 4 other articles before I finally stumbled into yours that actually resolved my issue – 2 criteria under the same column.
THANK YOU SO MUCH!!!
Best Wishes, Amy! Check out our Top Excel Tips
I never leave a comment but holy cow that was really useful and easy to follow. Thanks for sharing.
hi i just to try out on this formula, which I still could not get it done, I am doing a bank recon, at the end of the month. there are some cheque are not clear which mean the other side of the party has not bank in the cheque. now this few cheque will carry forward to next month. how am i going to do this formula out? I need to tally with the bank amount in my exit excel worksheet.
what if I have “Damage” and ”Faulty” in the same field and I only want to sum it once?
Try using the symbol *, like this: =SUM(SUMIF(B2:B21,{“*Damage*”,”*Faulty*”},C2:C21))
Lovely article Puneet, but I have question in my mind, how if this criterias in bracket {“Damage”,”Faulty”} change to cell refference?
Thanks
A doubt, and if it is to sum everything that is different from “Demage”, “Faulty”?
Hi Puneet,
Many many thanks for the multiple criteria use within SUMIFS.
I was struggling with my formula being all in ‘one cell’ using different criteria but same criteria range. Your solution and brilliant explanation using the SUM function solved that in seconds!
Many thanks once again.
Keep up the good work!
You are welcome, Irene.
THE ABOVE MENTIONED INFO IS VERY USEFUL TO ME, THANKING YOU
Great! I really like to read ur posts. Excel formulas and usage facinates me a lot. I work with data so ur examples help me a lot… Thanks.
Hey champ, I need your help to resolve my query. I want to make the criteria dynamic by getting the input from the user using slicer. When I have multiple criteria for same column I can use sum before sumifs as hard coded formula but I want to get the criteria from slicer based on the selection by the user. How to do this?
One word – amazing.
thanks!
Thanks for your word…
Building on the formula above =SUM(SUMIFS(D2:D21,B2:B21,{“Damage”,”Faulty”},C2:C21,”No”))….
Why can you not reference cells instead of using “Text”?
=SUM(SUMIFS(D2:D21,B2:B21,{B$6$,B$11$},C2:C21,C$11$))
Apologies, I re-read the article and saw the named range +CSE.
Very useful article. Thanks
What does “named_range” mean? What do you input as “named_range” when doing a cell reference instead of written words? So confused :/ Do you put the cell range of the values you want to look up?
Hi
I am trying to use this formula and its not adding correctly
=SUM(SUMIFS(D2:D21,B2:B21,{“Damage”,”Faulty”},C2:C21,”No”))
any clue what might I have done wrong ?
Hi Puneet,
Your tips above are highly effective, and I have a secondary challenge to apply here! Hope this post isn’t too old to be reignited 🙂
I may simply be thinking about this before having coffee every time but I can’t seem to solve it conceptually.
Effectively I’m looking for a way to dynamically ADD or REMOVE criteria from a SUMIFS without using multiple sums under IF conditions.
Example:
Name | Gender | Category | Sales
———————————————-
Dave | Male | A | 20
Mike | Male | B | 30
Tracy | Female | C | 10
Sam | Female | D | 80
I have many more categories in my working problem, including date ranges etc (effectively invoice data summarised).
I’m using a SUMIFS and I’d like to dynamically pick Male, Female or BOTH.
For both my assumption is that I should dynamically remove the criteria on Gender to include BOTH.
My only requirement is to do this in a single formula/array without an IF condition as mentioned.
Thoughts?
Cheers
Dave
Hi – What if I wanted to look at Damaged OR Faulty, and control for products, like only Product B and Product C. Can you have two OR sections?
Range_Name Critaria is Awesome and Very Helpful
Great Work Puneet
From Rizwan Planning Engineer
I’m glad you linked it.
Oh wonderful. I’m still mastering it inorder to know how to use it offhand.
That’s great. Keep it up.
Thanks for a great tip! Is it possible to use a dynamic criteria inside the OR Logic? i.e. use cell referance instead of {“Damage”,”Faulty”}?
Hey Shay, thank you very much for hitting me back. Your question is super awesome. So, if you want to create a dynamic reference, first you need to refer to a named range (the best way is to use a table reference) instead of adding your values in curly brackets. Secondly, you have to enter the formula with CSE as a proper array formula and, it works like a magic. Tell me if you need any further help.
Works Like a charm. thanks again for the quick and proffesional reply
Yo. ../
Hi shay,
Can u please help me to use dynamic criteria ??
Nice that is working without CSE. Just wondering if calculation time is faster then doing 2 seperate SUMIFs/SUMIFSs and then adding up. For larger datasets that can be an issue.
Yes, it is faster than using two different calculations. But, will effect you once you try to add more criteria.
Very useful tip, Puneet. Thanks for sharing.
I’m so glad you liked it.
Hi Puneet,
Formula is awesome. sum up using OR logic in sumifs for multiple criteria where its matching. i sum up multiple criteria where its matching but i need sum up where criteria doesn’t match under others head. can you please help me on this.