How to use SUMIF / SUMIFS with an OR Logic in Excel

Sometimes we need to make small amendments in formulas to make them effective.

Just like that, SUMIF OR.

It’s an advanced formula which helps you to increase the flexibility of SUMIF.

Yes, you can also do SUMIFS as well. But before we use it let me tell one thing about SUMIF.

In SUMIF, you can only use one criterion and in SUMIFS, you can use more than one criteria to get a sum.

Just 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, so all the criteria should meet to get a cell included.

...but...but...but.

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 current status about the shipment.

data table to use or logic to use in sumifs sumif

In this data, you have two types of products (Faulty and Damaged) which 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

=SUM(SUMIFS(C2:C21,B2:B21,{“Damage”,”Faulty”}))

apply formula to get or logic in sumifs

In the above formula, you have used SUMIFS but if you want to use SUMIF you can insert 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.

verify the total of damage and faulty using sumif or

How this formula Works

As I said, SUMIFS function use 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.

three parts of formula to use or logic in sumifs sumif
  1. The first thing is to understand that, you have used two different criteria in this formula by using array concept. Learn more about array from here.
  2. The second thing is when you specify two different values using an array, SUMIFS has to look for both of the values separately.
  3. 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 SUM function.

With the above concept, you are able to get a total for both types of product in a single cell. It will work the same with SUMIF and SUMIFS.

Using Dynamic Criteria Range

  1. The first is, instead of using curly brackets you need to use a named range (best way is to use a table) of your value.
  2. 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:

{=SUM(SUMIFS(B2:B51,Named_Range))}

using named range in sumif or

Multiple Criteria with Different Columns

Let me take you to next level of this formula. Just look at the below data table.

sumifs sumif or with multiple column data

In this table, you have two different columns with two different kinds of status.

Now, from this data table, you need to sum quantity where the product is damage return status is "No".

That means only those Damage and Faulty products which are not returned yet. And, the formula for this will be.

=SUM(SUMIFS(D2:D21,B2:B21,{"Damage","Faulty"},C2:C21,"No"))

sumifs sumif or with multiple different column criteria formula

Conclusion

Have you ever used this kind of formula before?

Share your views with me in the comment section, I'd love to hear from you and please don't forget to share this tip with your friends.

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube

22 thoughts

Leave a Comment

Your email address will not be published.

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

  2. 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?

  3. 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$))

  4. 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?

  5. 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 ?

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

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

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

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