Master the Excel functions COUNTIFS and SUMIFS to effectively filter and analyze data based on specific conditions. Learn practical techniques to accurately count entries and sum values without manually filtering your data.
Key Insights
- The COUNTIFS function enhances data analysis by allowing multiple conditions across different columns, unlike COUNTIF that is limited to a single column criterion.
- Using absolute cell referencing (pressing F4) is crucial when employing COUNTIFS and SUMIFS, ensuring consistent and accurate calculations across multiple rows.
- SUMIFS can calculate totals based on multiple criteria, as demonstrated by summing salaries of employees in the marketing department located in the south region, resulting in a total of $495,000.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Count ifs and sum ifs. Count ifs and sum ifs 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 count ifs and sum ifs if you were simply filtering the table. So 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 drop down and I'll filter for just accounts. When I click okay the count is 10.
If I just look at the bottom left hand corner I see 10 of 40 records found. That is count ifs. What is sum ifs? Sum ifs 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. So I'm now going to clear the filter. 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. So I'm going to type in equal count ifs.
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 count if you can only select one column. So I'm going to head over to the department column, control shift down, selected 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 check box for accounts.
I've selected it, I press ENTER and I get 10. Now I'll auto fill 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. When you're working with count ifs make sure you lock your ranges.
So 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. So what is sum ifs? So let's do our sum ifs exercise right now. I'll type in equal sum ifs 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 count ifs. Everything that comes after the first column for sum ifs is basically the count ifs 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 count ifs function. Close parentheses, I'll press ENTER.
952,000 which we've already determined is the correct value. 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? Count ifs to find the total number of people working in the south region.
So the criteria range is region. What am I, 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 and that entering south and entering marketing is like putting check boxes in the drop down when you click the drop down 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 altogether. So that's going to be equal to some ifs. Now the sum range is going to be earnings.
I'll press F4 just to be safe. And then the 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. Four hundred and ninety five thousand. So there is an exercise that we can do now.
Exercise three. 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 one hundred thousand dollars. So that's going to be a count equal count ifs.
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 one hundred thousand. So in double quotes, greater than one zero zero zero zero zero.
Close the double quote. Closed closing parentheses. There are three people in the accounts department who earn more than one hundred thousand.
Now for the sum ifs, I want to find out the salaries for those three people and the rest. Sum ifs. 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 count ifs.
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 one zero zero zero zero zero hundred thousand. Close parentheses. I should get three hundred and seventy six thousand.
And that's what I've gotten before when this is correct. And I'll auto fill down. 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 count ifs. I'm going to go over to the region column.
Control shift up 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.
So that completes the count ifs, sum ifs section. You can use this to quickly get counts and sums based on a single column criteria or multiple column criteria.