Excel SUMIFS formula with multiple criteria in the same column

E

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.

simple_sumifs _formula
Click to enlarge

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.

SUMIFS with OR condition
Click to enlarge

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.

excel table from range

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.

SUMIFS with multiple criteria
Click to enlarge

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.

About the author

Sudheer Tammini

Hello,
I am an 'Excel enthusiast' and early adopter of technology. I’d like to learn new things and solve the most difficult business problems using technology (trust me, it's fun). Besides running this website, I’m a full-time employee and have a Master’s degree in Finance.

Subscribe to our: Newsletter
Know more: About Me