Sometimes we need to make small amendments in formulas to make them more flexible and effective.
Just like that, SUMIF OR is an adanced formula which helps you to increase the flexibility of SUMIF.
Yes, you can also do SUMIFS as well.
Let's understand one thing first.
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.
When you combine OR logic with SUMIF/SUMIFS you can sum values using two different criteria at the same time.
So today, let's learn thing amazing formula.
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.
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.
In above formula, you have used SUMIFS but if you want to use SUMIF you can insert below formula in the cell.
By using both of above formulas you will get 540 in the result. To cross verify, just check the total manually.
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.
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.
Download this sample file from here to learn more.
So, now your formula will be:
Let me take you to next level of this formula. Just look at the below data table.
In this table, you have two different columns with two different kind 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, formula for this will be.
Get instant access to all the sample files from here.
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.