Join Dennis Taylor for an in-depth discussion in this video Tabulating information using a single criterion with COUNTIF, SUMIF, and AVERAGEIF, part of Excel 2013: Advanced Formulas and Functions.
If you're trying to tabulate data, particularly from large lists, you want to take advantage of a family of functions called CountIF, SumIF, and AverageIF. In this worksheet, we might want to know how many full time people work here. We could do this by way of a pivot table or possibly a more complex formula. But CountIF works quickly and easily. Equal Count IF. I'm using this in cell K2, and we're looking in column D for these entries, comma. And what are we looking for? Full time.
Now, if we don't have this in a nearby cell, I do, in cell L2, but if we don't, we would type "full time" press Enter, we get an answer. Since it's in a nearby cell, we'll just refer to that, enter, we have our answer. And we can double-click here to copy this down into the next three cells, as well, too. So, in all cases here, we have an account of how many hourly people we have, how many contract people, half-time and full-time, quickly and easily. Occasionally you might want to use this feature with wild cards.
Probably unlikely you'd want to total the full-time and half-time people. You can see what that total is right there, it's 489, but if we were doing this in an isolated case, we could type equal Count IF left parentheses. We'd be looking in column D, comma, and then double quote, asterisk, time. And asterisk is a wildcard meaning any number of characters preceding the word time and then a double quote. And there's our total, 489. By highlighting these two cells we see the total at the bottom, that's 489, so in a fact we're doing the same kind of thing here, but in a different way.
A similar use might be, we're looking at our list over there, we're just kind of curious. How many of these entries contain the letters? Tim, like in Timothy here. Maybe there are some Tims, we've got a few Timothys perhaps popping up. How many of those do we have? In that case, Tim is preceded by text is also followed by text. So in this example here, if we're doing a quick Count IF. We're looking in column A, and then within double quotes it's asterisk t i m asterisk double quote, and then we're done.
There are nine of them. And we could verify that with a quick use of filter. Just clicking within the data, use a filter, and then jumping into column A simply in the Search panel here, type tim and press Enter. And there's our list of all the Tims. There's actually one single Tim, and a lot of Timothys. But we've got nine entries there, and that's exactly what we found in our previous example. So, getting rid of the filter, going back to normal here. And viewing this once again. The asterisk on either side means ten preceded by and or followed by any number of different characters. So, you can also use this function in a more expansive way using Wildcard type symbol.
Now the companion to Count IF is called Sum IF. The idea with Sum IF is we might start off in the same way, in other words, we're looking for people who are full-time. But Sum IF has an additional argument, a third argument that allows us to say, in effect. Once we've found the full-time people, comma, let's go get their salaries and add them. In other words What are we spending on salaries for full-time people? We're looking in column D to see how many of those entries are full-time. It comes out of cell L2 here and when we do find an entry that's full-time, we go to column H, take that value, let's add up the full-time salaries. There they are.
And we'll simply double-click to copy it down there. There are the salaries. Look at the bottom of the screen in the status bar, 41,653,876 then we should see the same number if we click column H. And there it is, identical. Keep in mind the status bar will vary if you right-click it and possibly check or uncheck some of the options found in this section here. I tend to leave them all checked. I don't always need them all. But any time you highlight two or more cells.
And that includes the view highlighted column. You will get totals at the bottom. And it's real handy, at times, to do cross verification. So, getting back to SumIF. For years in prior versions, there was a SumIF. There was a Count IF. But no AverageIF. And a lot of users kept saying, give us AverageIF. Finally they have. If you don't have an AverageIF, you've gotta take SumIF and divide it by CountIF. But it certainly is a lot more straightforward here to say, and in fact, let's get the average of salaries for full-time people.
And there it is. And here too, double clicking will get the average salary for our other kinds of entries as well. And so, the example here in column K, right here, AverageIF. Remember, cell L2 has the text full time. If the entry in column D is full time, then let's go to column H. And what are we going to do? Average all of those salaries. So, we can see how CountIF, SumIF, and AverageIF give us easy ways to tabulate data from lists like this without having to sort the data or rearrange it in any way.
- Displaying and highlighting formulas
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Understanding the hierarchy of operations in formulas
- Using absolute and relative references
- Creating and expanding the use of nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the power functions: COUNTIF, SUMIF, and AVERAGEIF
- Analyzing data with the statistical functions
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Checking for errors with formulas