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.
COUNTIFS and SUMIFS. COUNTIFS and SUMIFS count and sum based on conditions. When a function has "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. 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 OK, the count is 10.
If I just look at the bottom-left corner I see "10 of 40 records found." 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. 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 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, CTRL+SHIFT+Down Arrow, 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 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. When you're working with COUNTIFS, 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 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, CTRL+SHIFT+Down Arrow 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, CTRL+SHIFT+Down Arrow, 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,500, 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? 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 and entering South and entering Marketing is like putting checkboxes 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 SUMIFS. 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. 495,000. 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 $100,000. So that's going to be 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's what I'm looking for in 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 counting 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 "10,000".
Close parentheses. There are three people in the Accounts department that earn more than $100,000.
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 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 greater than "10,000". Close parentheses. I should get 376,000.
And that's what I've gotten before when this is correct. And I'll AutoFill down. This is a bonus exercise.
How many employees are not in the East region? You can indicate "not equal to" by using "<>". So equal COUNTIFS. I'm going to go over to the region column.
CTRL+SHIFT+Up Arrow. I'll press F4. I'm looking for a count when the values are "<>East".
Close parentheses. I'll press ENTER. 28.
So that completes the COUNTIFS, SUMIFS section. You can use this to quickly get counts and sums based on single-column criteria or multiple-column criteria.