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

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.

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

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.

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

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"))

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.