From a common excel user to advanced user everyone will use sum formula very often. How about same sum formula, with little more advanced features..?

Confused..? ok, I’ll put it in simple words.

What if you can sum numbers based on your own criteria..? or maybe you want to add multiple criteria’s and then you want to sum for only those.

Well, with Excel SUMIFS formula everything is possible.

Basics : Excel SUMIFS formula

Growing business requirements need more complicated data analysis in almost no time to phase up with the competition. So, as an analyst how does you cater those requirements..?

I think the simplest way is to adopt advanced or untapped methods of analyzing data. Indeed, it’s a skill that pays off in a long term.

Without further delay, let’s dive in.

Syntax:

First things first, let’s understand syntax clearly.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Sum_range : Range or numbers that you want to sum

Criteria_range1 : It’s a range in which you have your most likely criteria’s

Criteria1 : This is where you’ll define your criteria, based on this the above formula will sum numbers

Excel Sumifs formula: Explanation with example

In my example below I’ve sales data by Region, Sales rep, Units sold & Cost.

Sumifs example#1

Question #1: I’d like to sum total cost for the East Region. To do so we can write simple single criteria based formula like below.

=SUMIFS(G:G,B:B,J5) = 6,002

Basically G:G is my sum range i.e. total cost.

B:B is my Criteria range – in this example we have region information in column B:B hence the same has been selected

J5 is my criteria, in my example I’m looking for “East”

Example#1 answer

Question #2: Now I’d like to know the total cost for Region “Central” & Product “Pencil”

Here is a formula for the above question.

=SUMIFS(G:G,B:B,J5,D:D,K5)

If you notice clearly, I’ve retained the same formula till J5, and then I’ve added second criteria.

In the above formula you’d see that I’ve selected column D:D as my second criteria range because that is where we have a list of products & K5 as my second criteria i.e. “Pencil”.

Sumifs_Example#2

Note: from second criteria you don’t have to define or select your sum range

Question #3: I’d like to know the total cost for “west” region, Product is “Binder” & units sold should be greater than or equal to 50

=SUMIFS(G:G,B:B,J5,D:D,K5,E:E,”>=50″) = 1,139

I’ve selected E:E as my third criteria range, in which we have numbers of units sold by product by region and so on.

And for my third criteria, I’ve added a logical symbol to indicate the units sold should be greater than or equals to 50.

Sumifs_Example#3

SUMIFS with wildcard conditions

Question #4: I’d like to know total cost for products Pen, Pencil & Pen Set

In this case we can use the above formula in two ways.

  1. you can write multiple criteria’s in your formula
  2. you can use wildcards like * , ? for the same, lets try with the second option

Here is my formula =SUMIFS(G2:G44,D2:D44,”Pe*”) =8,350

Sumifs_Example #4

G2:G44 : is my sum rage, that is where we have Total cost

D2:D44 : is my criteria range, we have all the products listed here

It means I’m looking for a product name, which starts with “Pe”: since we are looking for “Pencil”, “Pen” & “Pen Stand”, we should get sum of total cost for all the products.

Basically it means any letters after

Good to know information:

  • With SUMIFS function you can add as many as 127 criteria’s as per your requirement, however, in real life scenario the more criteria’s means there a room for error, hence keep it short

  • Any blank cell in criteria range and corresponding value will not be considered for calculation, hence it is always advisable to check bank cells, especially in your criteria range and fill them accordingly to get better & accurate results

  • We can use wildcard characters in SUMIFS formula; however, it needs to be enclosed with codes. For example “?” for finding one single character & “*” for anything after specific letters.

Conclusion:

Essentially Excel SUMIFS formula is one of the most used formula when it comes to Analysis, Dashboards & Ad hoc reporting. So, don’t you think it’s worth investing your time to learn & implement this formula for all the requirements?

Last, but not least, have you been using this formula for a while..? if you do so, would you please share your thoughts in the comments section below.