Join Dennis Taylor for an in-depth discussion in this video Using SUMIF, COUNTIF, and related functions for quick data analysis, part of Excel 2013: Managing and Analyzing Data.
When you work with large lists, it's sometimes necessary to come up with some summary information. Now, ultimately, you might be taking a look at pivot tables. But, there certainly are times when you simply need some totals out of a list, for example, like the one we're seeing here. There's about 400 names or so. And, different thoughts occur to us as we look at some of the various field names here. How many people are in the north building, for example? A series of functions. SUMIF, COUNTIF, AVERAGEIF, and the newer companions, SUMIFS, COUNTIFS, and AVERAGEIFS give us some of these tools.
Let's start with a COUNTIF. We simply want to know, for example how many names here, how many entries here contain Quality Control. The countif function equal countif, as you're typing functions of course, you don't need to capitalize them. Equal countif left parentheses. Where are we looking here for these? We're looking in column C, comma, and what are we looking for? Now if Quality Control was in a list off to the right, we could refer to it. Or if it's not we can simply within double quotes enter it right here.
The upper lower case isn't critical but of course you would probably match it anyway. The spacing certainly is, Quality Control, double quote. We don't have to type the right parenthesis. We simply want to know how many people or how many entries there are in Column C that contain quality control, and there are 44 of them. And we could certainly, if we had our list to the right, do this for all departments. That raises another question. How do we get a unique list of these entries? If we click column C, we can by way of the advanced filter feature on the data tab choose that option.
The list range is not columns A through G. So we can simply highlight this data here. Click column C. Criteria range, we don't need to worry about that at all. We do want to copy our results to another location. But the key box here is, Unique records only. So what we're about to get is a new list. We'll put this is column L, so copy to. Simply click in that panel, click out in cell L1 for example. We're about to get a unique list of the entries from column C in column L.
Click Ok. There they are. They're not alphabetized necessarily, they're in the same order that they occurred here. A quick way to alphabetized these of course, using some of the tools that we saw in previous movies. Simply click the AZ button right here. As long as the active cells within this list, the AZ button on the data tab, and there's our list. And so now, if we wanted to create a whole list of how many entries there are here, we could take a function like this. Let's make it instead referring to quality control, refer to cell L2, right there.
And Enter. Let's move this right here, perhaps make this column narrower. And then, as we double click right here, we have a count of how many people are in each department. Once again, it's the same COUNTif function over and over and over again, always looking at data in column C and then comparing it with data to the right here. So it gives us some of the tools that we might ultimately want to get out of a pivot table, but yet we can get them here. Not as much information perhaps, and not as efficiently, but nevertheless these are valuable tools.
Now, without going through necessarily going through all of these, but at least giving you some insight, SUMIF and AVERAGEIF both expand upon the idea of COUNTIF. And they both have an additional argument. It could be that we might want to see the average salary. These are all full time people who work in these various departments. If we want to see the average salary of people in a certain department, we'll use this function right here. Equal AVERAGEIF. As your typing functions too, recognize that if you do see them in the list here, since we hit the V, we will see this one.
We can just click here and tab it here into place. So AVERAGEIF, where are we looking here? The range is like before, we're looking in column C, comma, and what are we trying to match up with here. I'll use that first reference to the department right here, the ADC department right there, comma. Now an addition that we didn't see when using the COUNTIF function is to select the salary data. F. Now there certainly will be times when you are using these functions that you will have a distinct address here. For example, it might say, C2:C300.
If you do, this should be parallel, F2:F300, if you had that situation. But you simply refer to the entire column like this, it makes creating the formula simpler and easier and we'll have an answer here. Just like that. And here too. If I wanted that information to be placed next to this. I could put it right next to the column here. I should put it up here, and then copy it down here, and from here, double-click copy downward. And format that from the Home tab perhaps with the Comma button.
Probably don't need to see the decimals that much and that sort of thing, so every one of these is simply doing an AVERAGEIF. It's saying what's the average salary of these people in this department, in that department and so on. Some ifs would just do the total, and it would have the same construction as this. Now, moving ahead here. These three functions and in the letter S. And so they allow what are called multiple parameters. And let's scroll to the right a little bit because we don't need to see the employee names. But let's say we want to know if for example, we'll start with COUNTIFS.
We want to know how many people by department have a certain job rating. And possibly have been here so many years. So, let's introduce another column. I'll make this column a bit narrower by double clicking. Right click and Insert, a new column. Suppose we start with a COUNTIFS. Equal COUNTIFS. Now, you'll see the choice below criteria range one, criteria one. Ultimately, what we can have here are pairs, many, many pairs, up to 31.
And the so the first pair might be, we're looking at departments. Those are in column C, comma, and we want to count how many people in the ADC department, so we'll use our M2 reference up there. Comma. Now, if we had pressed Enter we would be hardly doing anything different than using COUNTIF, but we want to say, not only do we want to see the people in the ADC department, we want to know how many of them have a job rating of five, for example. So our next criteria pair would be column G Comma.
And since that is a value there, we can put in a five. If it were text, we'd put in either double quotes or refer to a cell. That's all we want to do. And so we're about to say, how many people in the ADC department have a job rating of five. And it's zero. Let's move this up top here. Copy down a few to check out some of our other departments. We'll see that there are ten of them in Engineering Maintenance. So looking at that there, have a job rating of five. If we wanted to rephrase this, let's say well how many of them have a job rating that's, that's four or more instead of the five here? We can put in double quote, greater than equal to four.
Double quote. And there is still zero here under ADC, but some of these other numbers will change now, and they're going to go up some of them, as we just did for this group here and we see what's happened. 17 people in Engineering Maintenance have a job rating that is greater than or equal to four. So there is a lot we can do with these, and, and we could have had more pairs, and more pairs, and more pairs. That's one pair. Here's another pair. If we also wanted to include information about years of service, that would be another pair, and so on. And one more of these, we'll do with AVERAGEIFS, same general idea.
Let me get rid of these here. And to make this a little more direct, let's, we'll do it right here, we'll start with ADC and drag it downward. We might want to know the average salary of people who have been here so many years. Equals AVERAGEIFS, left parenthesis and with AVERAGEIFS and SUMIFS the two plural variations here, we begin with what it is we're trying to tabulate, that's going to be the salary let's say column F and then we have our pairs. Criteria range one and then criteria range two.
So, we got two pairs now. The first one's going to be the department, that's column C, comma, is going to be equal to this ADC that we see over here in cell M2. So, we'll just click that into place. That's one pair. So, if we were to stop right now. If we had put in right parenthesis and Enter instead, we would have said this is the average salary of our ADC people. But now we want to say, in effect, we've, we figured out that we're averaging the ADC people, but let's do that only for the people who have been here more than 10 years.
So we're looking at column E right now, comma, and the criterion here is within double quotes. If we want 10 or more we could say greater than nine. That would work okay. Double quote. So what's the average salary of the people in the ADC department who've been here more than 9 years. Now, if there's nobody in that category, you'd get 0, but as we press Enter here, we see it's 59,000. Quick formatting here with comma, don't show the decimals, and drag this down to get some of the others, we see answers there.
You might get an answer like this, meaning there's nobody in the Engineering and Operations group. There are only four people there, but there's nobody there that has been there more than nine years. So different answers here. And you can begin to see, particularly if you are an analytical person, how these functions can give you a ton of information about the lists of data like we see in the one to the left here. These functions here: SUMIF, COUNTIF, AVERAGEIF, and their companions right here, go a bit farther in terms of their ability to calculate information, are extremely helpful in dealing with data like this.
- Multiple key sorting
- Filtering single and multiple columns
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX
- Converting lists to tables