Excel COUNTIFs and SUMIFs

Logic Functions are covered extensively in our NYC Excel courses. For those outside New York, find and compare the best Excel training near you and virtual/remote online Excel courses.

COUNTIFs and SUMIFs

SUMIFs and COUNTIFs count and sum based on conditions. When a function has an IF as a suffix, it acts as a filter. Let me show you what it would look like to perform COUNTIFs and SUMIFs if you were simply filtering the table. 

COUNTIFs

I'm going to click anywhere within the table. I'll go to the data tab, and I'll add a filter. 

Now, the goal here is to come up with the total number of times the word "accounts" shows up in the department column. So I'll click the dropdown, and I'll filter for just accounts. When I click "OK," the count is 10. If I just look at the bottom left-hand corner, I see 10 of 40 records found. 

SUMIFs

That Is COUNTIFs. What is SUMIFs? SUMIFs is when you go to a separate column and add up all the values in that column and come up with the sum. By selecting these values, if I look in the status bar I see 952,500. 

Exercise 1 

I'm now going to clear the filter, and I'm going to get the same results, but I'm not going to filter the table. So I'll head over to employees. The goal of this exercise is to figure out the total number of employees by counting the total number of times "accounts" shows up in the department column. I'm going to type in equal, COUNTIFs. 

We tell you to use the plural version because you'll have more options. If you use the plural version, you can select multiple columns as criteria ranges. Whereas with COUNTIF, you can only select one column. 

So I'm going to head over to the department column, control shift down, select the whole column, I'll enter a comma. Now notice that currently, I'm at the criteria range attribute in the function. As soon as I enter a comma, I now get to select the criteria that's similar to clicking the checkbox for accounts. 

I've selected it, I press enter, and I get 10. Now I'll autofill this down, and this supposedly should give me the total count of employees for all of the departments. 

Now let's take a closer look at this. I'm going to go over to sales, and I'll press F2. Notice that the column is not including the first five rows, and that's because I shifted five rows down and this is relative reference. So when you're working with COUNTIFs and SUMIFs. Make sure you lock your ranges. 

I'm going to go back to the original function. I'll press F4 to lock that range, press enter. Now, if I double click, take a look at what happens to the last two values when I lock the range. That's a big difference. 

Exercise 2

So what is SUMIFs? Let's do our SUMIFs exercise right now. 

I'll type in equal SUMIFs, and the first thing you're being asked is to select the range that you want to sum. So I'm going to head over to earnings, control shift down, and I'll press F4. Now I'll enter a comma, and here is the benefit of understanding COUNTIFs. Everything that comes after the first column for SUMIFs is basically the COUNTIFs function. 

So I'm going to go now to the department column, control shift down. I'll press F4, and the criteria is going to be accounts — just like I selected "accounts" for the COUNTIFs function — close parentheses. I'll press enter, 952,000, which we've already determined is the correct value. 

So now we are actually going to take advantage of the ability to use multiple columns. The question we're being asked here is how many employees from the south region work in the marketing department? 

COUNTIFs to find the total number of people working in the south region. So the criteria range is "region." I'll press F4. What am I looking for in the region column? I'm looking for south, so I can type that value in. 

Next I want to find how many times "marketing" and "south" both show up together. So I need to go to the department column and also filter for marketing, so I'll press F4, comma. Entering "south" and entering "marketing" is like putting checkboxes in the dropdown when you click the dropdown for both those columns. I'll press enter. There are four people who work in the south region and the marketing department. 

Now I want to find out how much in terms of salary those four people earn all together. So that's going to be equal to SUMIFs. Now, the sum range is going to be "earnings." I'll press F4 just to be safe, and then that first criteria range I'm going to choose is region. I did that last time, so I'll do that here, F4, and I'm looking for the south region, then I'm going to head over to the department column, F4, and I'm going to look for marketing. I'll press enter, 495,000. 

Exercise 3

So there is an exercise that we can do now, Exercise 3, and there are different ways of indicating criteria. Criteria can be indicated as being greater or less than a certain value, and we'll do that right here. 

For our first column, we want to get the count of employees who are earning more than $100,000. So that's going to be a COUNT, equal, COUNTIFs, and the criteria range. First of all, I'll go to department. I'll press F4, definitely needed in this situation, and there is what I'm looking for for that first row. I'm not going to lock that because I want that to shift every time I move down a row. 

Now, I'm not interested in the salaries, the count of everyone in the accounts department solely. I'm also interested in getting a count only when the earnings are greater than $100,000. So in double-quotes, greater than $100,000, close the double-quote, closing parentheses. There are three people in the accounts department who earn more than $100,000. 

Exercise 4

Now for the SUMIFs. I want to find out the salaries for those three people and the rest. SUMIFs, I'm going to go over and select what I'm summing first. I'll get that out of the way, F4. Next, everything is similar to the COUNTIFs. I'm going to go over to the department column, F4, and I'm looking for the value over to the left. And only those people whose salaries or earnings are, F4, greater than 100,000, close parentheses. I should get 376,000, and that's what I've gotten before when this is correct, and I autofill down. 

Bonus exercise

This is a bonus exercise. How many employees are not in the east region? So you can indicate not equal to by using the less than and greater than symbols. So equal, COUNTIFs, I'm going to go over to the region column, control shift up and down arrow, I'll press F4. I'm looking for a count when the values are not equal to east, close parentheses, I'll press enter, 28. 

Recap 

So that completes the COUNTIFs, SUMIFs section. You can use this to quickly get counts and sums based on a single column criteria or multiple column criteria.