SUMIFS, AVERAGEIFS, COUNTIFS – Make Intelligent Calculations

Check out my SUMIFS video tutorial here:

These three functions can be some of the most robust and powerful functions in excel and a strong addition to your arsenal.  I’ll discuss what each one is and how to use them, and then we’ll dive deeper into each component of the function. Lets start with SUMIFS.

How SUMIFS works

The great thing about SUMIFS (and why I never use its inferior sibling, SUMIF) is that you can use multiple criterions in your function, providing great flexibility when you are building reports and dashboards. Need sales numbers for individual employees broken down by region and by month? Need a dashboard that tells you which collectors are making the most calls? Or, Maybe you are building a P&L for your cousin’s wedding stationary business? SUMIFS has you covered for that and more!

How COUNTIFS works

COUNTIFS is useful whenever you need to count something, obviously! From my experience, I tend to use it more when I’m nesting it with another function to create mega-useful formulas. For example; suppose I want to allocate my Internet cost evenly across employees within a specific department (ie. Marketing). I would need to count, manually, how many employees in my list are part of the Marketing department and divide the total cost by that number. That’s both tedious and mind numbing, especially when you are dealing with large datasets.

COUNTIFS is a nice trick to have; it allows you to count how many employees you have in any department. In our example, you can use COUNTIFS to count the number of marketing employees, and then easily divide that cost by that number.  Easy does it!

In addition, if you later add a new Marketing employee to the list, the formula could automatically include them in the count, and then divide the total cost evenly without you ever breaking a sweat!

How AVERAGEIFS works

Just like SUMIFS, AVERAGEIFS uses multiple criteria to average a set of numbers. Remember that sales report you needed, broken down by region and month? Well, now you can also add averages and provide even more insight on the sales staff.

The Syntax

Lets dive into how these functions are constructed:

=Sumifs/Averageifs(Sum Range/Average Range, Criteria Range, Criteria)

=Countifs(Criteria Range, Criteria)

Sum Range/Average Range – In order to begin writing your function you will select the range of numbers you want to add or average (depending on whether you’re using SUMIFS or AVERAGEIFS). The data set you will use for this is usually located in a singular column.

Criteria Range – Criteria Range may sound confusing, but it’s really no big deal. Do you have a column that contains, for example, employee names, names of cities or states, billing statuses, age, etc.? All you have to do is select this range; excel will need it to determine which adjacent cells to add, count, or average (based on your next instruction).

Criteria – You’re almost done! Remember the criteria range you used in the last part? This time, you will tell excel exactly which of these criteria you want it to use when executing the formula.

Lets get Practical with SUMIFS

Lets dive into a practical example. You are working for a company that develops mobile apps and you have generated a list of daily download numbers for three of the company’s apps: Fire Red, Water Blue, and Earth Brown.

You need to get total downloads for Fire Red between 06/01/16 and 06/15/16, broken down by day.

Lets look at what I entered a bit more closely

=Sumifs(Sum Range, Criteria Range, Criteria)

=Sumifs(Column C, Column A, I2, Column B, H2)

Column C = Sum Range

We’re going to total up the daily downloads

Column A = Criteria Range [1]

We need excel to only include downloads for Fire Red

I2 = Criteria [1]

We have already entered the name of the App we need totals for in cell I2, so we can just reference this cell. We could also enter ‘Fire Red App’ in quotes and it would work

Column B = Criteria Range [2]

We also need excel to only include certain days

H2 = Criteria [2]

We also entered the days that we need in Column H, so we can just reference these cells.

Note: Remember to use the correct cell references!

The result of the formula is 710 total downloads for Fire Red on 06/01/16. Lets check our work by looking this up on our table.

Looks like the function is right. We can now drag it down and get our total.

The great thing about these functions is that you can make them both flexible and scalable. Lets say you instead need Water Blue App download figures for 7/18/16 – 7/29/16. No problem, just change the labels