Very often, you will have to construct an excel SUMIFS formula with multiple criteria based on the same column values.
Which is equivalent to the SUMIFS formula with multiple OR conditions.
I guess you may have tried it…but find it difficult to achieve. Because the Excel SUMIFS formula does not have any such option.
So let me explain how to achieve this using a few tricks.
Excel SUMIFS with multiple criteria
If you are reading this article then I’d assume you already know how to use SUMFIS in general, if not then here is my SUMIFS tutorial.
Now let’s define a problem: I have a Sales Dataset for a supermarket (you can download it here) which has sales data by various dimensions like Region, City, Postal Code, State, Product, etc.
Let’s create a simple SUMIFS formula to extract Sales & Profit data for a City = Los Angeles and Category = Furniture.
As you can see this is more or less straightforward.
Just by using the basic SUMIFS formula, you can get the data you wanted.
But what if you want to extract Sales & Profit data for City = Los Angeles, Houston and Category = Furniture
Technically you would want to write three conditions…which works fine, but you don’t need to complicate the formula with multiple conditions.
I’ll show you how to write a simple formula for the above criteria.
So what I have done here?
Step1: Just wrote simple SUMIFS formula like above
Step2: In criteria 1, instead of referencing into a cell I have opened curly braces and wrote our conditions one after another.
Step3: Then for the second criteria I just linked cell reference like above (no curly braces)
Step4: At the end, I’ve wrapped the entire SUMIFS within the SUM formula
Essentially I have taken cues from Excel array formulas.
Is this efficient?
No, it’s not.!
Although this formula looks better than writing multiple conditions, it’s not good enough.
For example, you can use curly braces only for Criteria 1, but in case you have multiple conditions like Criteria2, Criteria3…Criteria n then this won’t work.
Even in our above example, we can’t use curly braces for ‘Category’.
Better way to do sumifs with multiple OR Criteria
The better and more flexible way to write multiple OR conditions in the SUMIFS formula is by using excel array formulas.
Don’t fall off from your chair…array formulas are not that complicated. I’ll explain in simple steps.
Step 1: You must decide all your criteria and document them in a dedicated table
Step 2: Then select your criteria data and convert it as an excel table.
Step 3: For convenience, I have renamed the excel table as ‘MyTable’ (you can do this from the table design section)
Step 4: Start writing the SUMIFS formula like before but in your criteria 1 select data from the table like below.
SUMIFS(Orders!R:R,Orders!J:J,MyTable[City]
Step 5: Same logic applies to Criteria 2 & Cretiaria 3 like below.
SUMIFS(Orders!R:R,Orders!J:J,MyTable[City],Orders!O:O,MyTable[Category],Orders!E:E,MyTable[Ship Mode])
By using a table, you don’t need to worry about extending cell ranges. Of course, this is not the only way, you can also use ‘named range‘ for this purpose.
Once you are done with the SUMIFS formula you will notice that your formula isn’t working the way you wanted
Step 6: Now wrap your entire SUMIFS formula in SUM like below.
=SUM(SUMIFS(Orders!R:R,Orders!J:J,MyTable[City],Orders!O:O,MyTable[Category],Orders!E:E,MyTable[Ship Mode]))
But still, it’s not working, right? just hold your breath there is one more step pending.
Step7: Place your cursor in the formula bar and press [Ctl+Shift+Enter] that’s it…you should see the result.
As you can see I have now got 17,061 based on all my conditions.
Now it’s your turn, just try to implement a similar formula for Profit, I’ll give you a hint…the answer will be 5,620.
So, Is it worth learning this.?
My answer is resounding YES…let me explain.
Just look at the conditions that I have laid out, if you have to get this sum manually then you will have to use Pivot Table and apply various filters.
But with this one formula, you can just compute the sum based on various conditions.
Imagine, if you are building a nice little dashboard for your management, you just need these types of formulas to compute numbers efficiently.
Sure, Pivot tables will work, but how many pivot tables can you create..? how difficult it is to monitor all the filters…just think about it.
Short-term pain is a long-term gain.
Conclusion
I hope this tutorial has given you enough motivation and direction to learn Excel SUMIFS with multiple criteria or conditions.
If you want to master any concept you just need to practice again and again, so give it a try as soon as you finish reading this tutorial.
You can download my workbook from here.