Count how many entries are both full time and job rating five, and we could add a third if we wish. We've got two pairs, so in English we're saying the following. If this were Countif, this is all we could do, but it's Countifs, so let's put in another pair, and it might be, for example, column G, comma, five, that's another criteria pair, and we can have another pair and another pair, on and on and on, 127 different pairs. Now that's one criteria pair, think of it that way. =countif( we're looking in column B, comma, to see who's full time, and once again I'll use cell J3 since it has that phrase in it. So let's count how many of our full time people have a job rating of five. We want to be able to see in our examples here the full formula without line wrap. Let me make column H a bit narrower here. So for example, somebody might be saying, how many of our full time people have a job rating of five and have been here over 10 years and have a salary over a certain amount, and so on and so on and so on, so we can use multiple criteria here. Now, the three companions that we see down here, Countifs, Sumifs, Averageifs, allow us to essentially do the same kind of thing, but it allows for multiple criteria. I'll simply copy this down, make some adjustments here, change sum to an average, and we're looking here not at J4, but at J3, that's Full Time, and here we're doing the average salary for our full time people, and there it is, Averageif. And Averageif, as you might guess, is pretty similar. Once again, Sumif, where are we looking? Column B, what are we looking for? The phrase Full Time, and what will we do with that? We'll pull out the data from column F and add them all up.
And if we simply want to show this for the full time people, instead of that 13 million number, instead of J2 here, we'll simply make that be J3, and now we'll have the total for the full time people. We want to format that quickly up on the Home tab, click the comma button, let's not show the decimals. So this is Sumif, we're gonna get an answer here, the total salaries here for the contract people. Always think of these two outer entries here to be in parallel, and when we're using entire column references, the big advantage is if this list grows or shrinks, we're going to have an answer. Now, in a real life situation, if we're using an exact set of addresses here, for example, if this is B2:B400 over here, this should be F2:F400. But now we put in a comma, and where are we trying to get those salaries from? They're in column F, so I'll click column F. We begin with the same idea, we're looking in column B, that's where the status entries are, J2 has the word Contract, so we're looking for the contract people. Suppose somebody says, what are we paying our contract people? Sumif instead of Countif. I'm also gonna copy this downward to make the creation a bit faster here.
I'm going to put a space in front of this and simply leave it here this way. Now, Sumif and Averageif start off in essentially the same way. If it's contract instead, if I'm curious about that, I'll simply click on J2 here, how many contract people do we have? 181. =countif( where are we looking? We're looking in column B, comma, what are we looking for? How often do we see full time? I can type double quote, full time, double quote, nothing wrong with that, but I do have it in a nearby cell, I put this here earlier, so I'll simply click on cell J3 and Enter, we've got our answer. Now, I've got the phrase Full Time up here and Contract just so I don't have to type them, but I'll show you how in a formula here we can use either approach. We're looking at the data here, and the question has come up, how many full time people do we have? Well, let's count them using the function Countif. In many ways, these give you some of the same answers you would get if you create a pivot table based on this data. These functions are ideally used to tabulate data out of large lists. In this worksheet called Countif Sumif, I want to make you aware of a family of functions, starting with Countif and Sumif and Averageif and their related plural counterparts, Countifs, Sumifs, and Averageifs, and two relatively new functions added in 2016, Maxifs and Minifs.