Shares

Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

# SUMIFS and SUMIF with an OR Logic

Today, you will learn a simple trick to increase the power of SUMIF and SUMIFS.

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

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 be meet to get a cell included.

But today, in this post, I will show you a simple way to use OR logic with SUMIF and SUMIFS instead of AND.

## But Why We Need OR Logic?

Let me show 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.

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 with SUMIF/SUMIFS then you can get total of both of the products with just one formula.

## Apply OR Logic

In any other cell, where you want to calculate the total, insert below formula and hit enter.

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

In above formula, I 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 above formulas you will get 540 in the result. To cross verify, just check the total manually.

## Explanation: OR Logic with SUMIFS and SUMIF

As I said, SUMIFS function use AND logic to sum values. But, the formula which you have used above include an OR logic in it.

To understand this formula you have to split it in three different parts.

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 above concept, you are able to get a total for both types of product in a single cell. It will work same with SUMIF and SUMIFS.

And, you can also further expand your formula by specifying product name in second criteria in SUMIFS to get the total for a single product.

## Conclusion

The best part about using OR technique is you can add as many as criteria in it.

If you want to 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.

I hope you found it useful and it will help you to done your work more efficiently.

Now, tell me one thing. Have you ever tried to do that? Share your views in the comment box with me. I would love to hear from you.

## Related Tip

• Very useful tip, Puneet. Thanks for sharing.

• Puneet Gogia

I’m so glad you liked it.

• GraH

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.

• Puneet Gogia

Yes, it is faster than using two different calculations. But, will effect you once you try to add more criteria.

• Shay Bar-Yehuda

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”}?

• Puneet Gogia

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 (best way is to use a table reference) instead of adding your values in curly brackets.

Secondly, you have to enter formula with CSE as a proper array formula.

And, it works like a magic.

Tell me if you need any further help.

• Shay Bar-Yehuda

Works Like a charm. thanks again for the quick and proffesional reply

• Puneet Gogia

Yo. ../