Using SUMIF, COUNTIF, and related functions for quick data analysis

- [Voiceover] 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. These functions are ideally used to tabulate data out of large lists. In many ways, these give you some of the same answers you would get if you create a pivot table based on this data. 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. 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. =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. 212 entries are Full Time. 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. So either way, easy to work with. Now, Sumif and Averageif start off in essentially the same way. I'm going to put a space in front of this and simply leave it here this way. I'm also gonna copy this downward to make the creation a bit faster here. Suppose somebody says, what are we paying our contract people? Sumif instead of Countif. 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. 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. 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. 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. So this is Sumif, we're gonna get an answer here, the total salaries here for the contract people. We want to format that quickly up on the Home tab, click the comma button, let's not show the decimals. 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. 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 Averageif, as you might guess, is pretty similar. 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. 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. 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. Let me make column H a bit narrower here. We want to be able to see in our examples here the full formula without line wrap. So let's count how many of our full time people have a job rating of five. =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. Now that's one criteria pair, think of it that way. 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. We've got two pairs, so in English we're saying the following. Count how many entries are both full time and job rating five, and we could add a third if we wish. Maybe years of service here, column E, comma, and if you want to use greater than or less than, you need to put it within double quotes. So how many of our full time people who have a job rating of five have been here 10 or more years? "">=10"" So we're all set here, except for the fact this is Countifs, you need the S there. Again, think of these as plural. We've got multiple criteria here. Countifs, Enter, we've got our answer. There are 34 people in this list who are full time and have a job rating of five and have been here more than 10 years. Now we're not seeing them, of course we could do this with a filter. But I think you can see from the example here how we can come up with multiple criteria using criteria pairs. but as you might expect, Sumifs and Averageifs work along the same lines, but in comparison to what we saw with the singular versions up here, there's perhaps a surprise as we try one of these. I'll simply use Averageifs in the example here, and I'll just copy this down here so we don't have to do a whole lot of re-typing. I want to use Averageifs here, and let's say I want to use the same criteria that I used in Countifs, but I want to be tabulating here data from the Salary column. What's the average salary of all the people who meet these three criteria? The ones who are full time, have a job rating of five, and have 10 or more years of service? Right after the opening parentheses, we refer to the data we're actually tabulating, in the example here it's coming out of column F, comma. So that's what we're about to tabulate for all entries where the column B entry is equal to, and we'll need to adjust this reference too since we dragged it down, equal to full time, that's gonna be J3, and also their job rating is a five, and also their column E entry, that's years of service, is 10 or more. What's the average salary of all these people? And there it is, and there too, we probably readjust the format, we could simply copy this one from above using the right mouse button, you can drag the bottom edge of that down to here, and when the menu pops up, Copy Here as Formats Only. So we're seeing different examples of how to use these functions to tabulate data from large lists. These come in really handy at times. They're perhaps not as good as the pivot table ultimately is, but you don't always need to create a pivot table, and I think you can see from the answers that we're getting from these functions how powerful they really are as we analyze the data in a list like this one.