Join Dennis Taylor for an in-depth discussion in this video Tabulating based on a single criterion with COUNTIF, SUMIF, and AVERAGEIF, part of Excel 2011 for the Mac: Advanced Formulas and Functions.
- Excel has three functions that allows us to tabulate data from lists based on a single criterion. They're called COUNTIF, SUMIF, and AVERAGEIF. They're very similar in nature. In this particular worksheet called single criterion, we've got a list of some 700 names here. We might simply want to know for starters how many full-time people we have in our list here. We can do this by way of a filter but that would require our data to change its appearance. We don't necessarily want to do that.
Let's simply click in column I here. I put the phrase "Full-Time" and our other three kinds of statuses out here ahead of time. Not necessarily a requirement. Let's imagine for the moment they're not there. How would we count the number of times that Full-Time appears in column C? The function is =countif( where we're looking as always if it's an entire column and there's nothing else in the column. Let's just select the column letter, comma.
Now, if we don't have Full-Time in the nearby cell, we put in "Full-Time", just like that. Return and there's our answer, 393. Now, we can also at different times, use wild cards in these kinds of formulas. For example, we see departments over in column B and we can take this department, that department, and countless number of entries. But what if we wanted to count how many entries there contain the word Training? We don't see any necessarily right now but there are different departments there that contain the word Training.
Let's look in column B instead. Now we don't necessarily know where the Training is it at the beginning of the entry or at the end. So let's put in double quote and use the asterisk symbol for wild card and then Training. Now this means the word Training can be preceded by any number of characters. If other characters might follow Training, maybe the word Group or Organization or Department or something like that, let's put another asterisk to cover that possibility as well too, and then a double quote.
So we're looking for how many entries in column B contain the word Training? As I press return, there are 40. Now again, we're not seeing them. So a quick check of this by a way of filtering. I'm going to go to column B, click on one of the cells here and then go either to the Data menu on the top of the screen or the Data tab in the ribbon and choose filter. I'll click the drop arrow here and we can choose one here. How about Contains? And then right here, training.
There we are and we see different kinds of Training Groups there. The number of entries we have here as I drag across them. is going to be 40. That's the number we saw earlier. So as I drag across those, we could click on the bottom of the screen, the status bar here, and verify. Now we do see the number 40 there. So there are 40 different entries that contain the word Training. This is simply to verify. Often we don't need to do this sort of thing. So I'll simply remove the filter and then return to the top. Double click the top edge of the cell. So once again, the COUNTIF function is counting how many times Training appears here.
Now, going back to our previous example, where we were simply checking column C to see if column C has Full-Time in it. Then we will know how many times that appears. Earlier, we'd put Full-Time between double quotes. But since it is in a nearby cell to the right, I'll simply click on that cell. For the moment, we can barely see it. We don't need the + there nor the double quote. So a 393. Let me scroll to the right briefly so I can see that a bit better. There we are. So that's picking up the data from cell J2.
We're checking column C to see how many times Full-Time appears there. Since we have our other three kinds of status here, we'll double click. That's how many Half-Time. We have Contract and Hourly. That's the COUNTIF function. One of its counterparts is called SUMIF. What's different about SUMIF, we know that the word sum is going to be different. It's going to be tabulating things but differently. But the SUMIF function has three arguments to it. Again, we might start by saying, "Let's look in column C for the Full-Time people." But what do we want to do with that data? Every time we find a Full-Time person, let's pick up a corresponding set of data that has the compensation amount.
So comma, let's get that data from column F. Now if you are using an exact addresses, for example, this is something like C2, column C 740, you want to make sure that this is a parallel set of cells, possibly in a column or you might have two situations where you're doing this in rows. But by using an entire column reference, we don't worry about that too much. We are about to see the total salaries or the compensation amounts that is, column F for Full-Time people. There it is. We could format that pretty quickly with ctrl + shift + !.
That's a numerical format. I'll double click to copy this into the next three cells. The same count total for the others. Just double clicking one of them so we can see it. SUMIF, again, based on column C, this time whether it's Contract or not and then the compensation amount is coming out of column F. Now a quick check on this. We could highlight these cells right here. If we go to the status bar, click the drop arrow and make sure we're looking at sum here. That total is 41,653,876.
If I click column F, we should be seeing the same number. and we are. So that's a verification this is working just fine. The AVERAGEIF function, similar in nature to SUMIF, is in fact the SUMIF total divided by the COUNTIF total. But it makes more sense simply to have an AVERAGEIF so I'll change this to average and complete that entry. Then copy into the next three cells. So the AVERAGEIF function, simply like the SUMIF function, initially establishes data based on the criterion here that we're looking in column C for just the Full-Time entries.
When we do, we then pick up this corresponding data from column F and we average that information. So the average compensation for our Full-Time people is 61,000. Half-Time, 34,000 and so on. So these are easy to use. They're direct and straightforward. Also, we can use wild cards where appropriate. The next movie, we'll discuss how to use multiple criteria with the companion set of functions.
- Using absolute, relative, and mixed references
- Using the Formula Builder
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- 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
- Calculating payment
- Performing basic math
- Determining dates and times
- Editing text with functions
- Analyzing data with arrays