Use COUNTIF to count the number of occurrences of a text entry or value within a range. Use SUMIF and AVERAGEIF to expand this functionality by tabulating data in a parallel range. Where possible, use entire row references or entire column references to allow the function to work with expanded data.
- [Voiceover] In this workbook called zero six power functions we're on the first sheet, single criterion. This refers to a family of functions, count if, sum if, and average if, that allow us to tabulate totals based on a condition. A similar set, we'll see this in the next movie, multiple criteria, includes the same functions that end in the letter s. Let's first talk about what we're looking at here in the data. We've got a list here from columns A through G. Quick reminder if you click on a cell, double click it's bottom edge it will take us to the bottom or at least it will take us to the first blank cell.
In this case we see what's happening. Over 700 rows of data. We simply want to know, for example, how many full time people do we have. So, in column J off to the right here the function equal count if left parenthesis, we're looking in column C and once again by using an entire column reference the advantage is if this list grows or shrinks we won't have to rewrite this formula, count if comma. If I didn't have this information in a cell to the right, right now I would type double quote, full, space, time, double quote, and I would get a correct answer.
But saving some typing time and I've got the four different kinds of status listed over in column K I'll simply click K2 and press control return and it's 393. Double click to copy this down the column and there's the function. Simple, straightforward, it could also be used for example if you're counting a column in a questionnaire. How many yeses. How many nos. That sort of thing. Easy to work with. Not a big problem. We could also use wild cards here too. Suppose, for example, we're saying out of these departments here I don't know the list that well, how many of these have training in their name.
There's professional training group. There's sales training. Looks like training might appear at the end of the list. It could be in the beginning. Let's use a wild card in such a way that we count the number of occurrences of training. So, instead of K2 here we'll put in double quote. Now I could type training, but first I want to put in an asterisk because training might be preceded by other data. For example, over in cell B4, professional training group, so following the double quote I put in an asterisk and then training.
And since training might be followed by words, like it is in cell B4, we put an asterisk after training as well. And the asterisk means that there could be zero, one, or multiple characters that follow training. We wanna cover all bases here. So, I'm about to press return, but instead of the reference to column C this needs to be in column B. There we are. And control return. There's the answer, 41. 41. Now we could verify this quickly. You may or may not be familiar with how to use a filter but if we click on the list over here, go to data and then click filter, click the drop arrow here we could choose, for example, here under search, I'll just type in training and immediately we're seeing in the background all those entries in column B where the word training occurs.
It looks like it occurs in at least three different situations. But lower left corner of the screen says what, 41 out of 741. Now because of the filter's active right now we're not seeing our total. But that's the number that we had seen earlier. So let me remove the filter and again remember that number 41. 41 of these entries have the word training in them. Let's just remove the filter and there's that answer we got over there. So you can use wild cards. Now you could also use the question mark to refer to a specific character. Sometimes that gets a little bit obscure but never the less you can use that as well too.
But the main thrust here is we used the count if function to get a count. Initially we did this with column C for status, but we've shown you this one example here looking at column B. Now how about the function some if. We'll start with the second entry here. Since this is pretty much what we want to do but we want to make an additional change, when you use some if you're saying in effect, let's say that every time we find an entry, in case we're looking at half time, let's then pick up a corresponding range of cells and we put this just after the K3 entry, and tabulate the total, for example, the data in column F.
So, what's the total expenditure here for half time compensation. There it is. It's not formatted, so on the home tab, one way to do this of course would be the comma button. We don't need to see the decimals let's say. Good enough for now. So, some if goes an additional step. Now these examples I'm using entire columns. Not only for ease in creating these but because as the list grows or shrinks I won't have to change it. But there could be situations where the range is a specific set of cells that doesn't change and, so, for example here, I could type in C2 colon C742, that's the last row, but if I'm doing that I want to make sure that that the other column reference, or the other location out here for the totals, is going to correspond with that.
So, F2 colon F742. So, you do want to be careful when you're using those lists like that. Now again it would have been easier to use the previous format, once again, as I press control u to expose this, nothing wrong with that, in fact that's accurate, but if the list grows I'll have to change it. So, as I press command z, go back to the previous display, I would double clip to copy this down and also copy it up, if I wanted the total for all these. So, that total, and I'll highlight it there is 49 million, we see this at the bottom of the screen in the status bar, 49,984,653.
If I click column F it should be the same. And it is. So we're verifying it that way. So the sum function goes one step further than count if. And so does it's companion average if. Same general idea. The same layout. Same structure as sum if. Complete the entry there. I'll double click to copy these down. That's the average compensation for each full time people. So again the logic is we're looking in column C and trying to find any entry that's equal to full time and anytime we do find that entry then we go into column F and pick up the compensation amount.
And we do that one by one, we add them all up, and divide by the number of entries, of course that's what average does. So three valuable tools here, count if, sum if, and average if.
- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets