Join Dennis Taylor for an in-depth discussion in this video Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF, part of Excel 2010: Advanced Formulas and Functions.
Sometimes you need to create quick summary totals on a worksheet. In this worksheet called SingleCriteria, as we look at data here, you can imagine the question popping up: how many contract people do we have here? How many full time people do we have? We want to know this information. There are about 300 rows of data here. Sorting is not going to help us as that much. It will pull them all together, but we want some quick totals. There are three interrelated functions: COUNTIF, SUMIF, and AVERAGEIF. They are very similar, but work slightly differently.
To get a quick count, we want to use COUNTIF. Now maybe we want to put this information temporarily in column F. The COUNTIF function: just based on its wording, you can probably sense pulls together the concept of counting, along with an IF function. It isn't strictly an IF function, but it uses that kind of logic. So a different way to state this might be, in English: if an entry in column B equals contract, then let's count it. We use that as an example.
So where we are looking? We are looking in column B. Comma. What are we looking for? Two ways to do this. We might within double quotes put in where we are looking for. Now we can do this with numeric information, but first time look here, we're looking a contract. We are looking to see how many times this appears in column F. 78 times. We have got 78 contract people here. I have got a list over here of the 4 kinds of statuses that we have here, so maybe we'd make this a little bit more expansive.
Instead of using this, why don't we simply refer to Half-Time over here? Then we'll both copy this up and down. So we have 36 half-time people. We saw earlier it was 78 contract. 20 hourly. Drag this upward. So what we have got here is four bits of information. Just looking at this one for the moment, we are looking in column B, and how often do we see what we currently see in H1--in other words, how many full-time people do you see? 162. That works out pretty nicely. If it is a text entry, refer to it within double quotes as we first saw, or by cell references we saw it this way.
Now, what if it's a numerical entry? =COUNTIF. Looking now in column C. I doubt if you really want to find out who is here exactly 10 years, but if you did, you could click column C, comma, 10. I don't know if there are any, but we will find out. 16 people have been here 10 years. I think more likely what you would want to be able to do here is to pick up a range. You notice off to the right I have got this indicated here just for reference. Suppose we want to know how many people have been here more than 10 years.
Here we need to actually embed this within double quotes. Double quote greater than 10 double quote. 146. So different ways to use this, and I think you can see how fast this is and how straightforward it is. Now the companion function to this, the first one is called SUMIF. We might want to know, after having found out how many contract people there are here, how much their salaries add up to? The SUMIF and AVERAGEIF functions start with the basic concept of COUNTIF and then extend it into different kinds of information.
For example, we are trying to find not just how many people are contract, we got that part nailed already. Now, I am just going to click on cell H3 here to pick up the word Contract. Now we are about to say, every time we find someone who's contract status--comma-- let's go into column D, grab that salary, and keep adding these up. Or in other words, how much are we paying these contract people? And sure enough, if we wanted to get this for the other list, will give the copy that downward or change the reference, and so on.
So the SUMIF function has three arguments to it. It starts off in the same way that COUNTIF does. Here is where we were looking. Here's what were looking for--in this case contract. Let's go to their salaries and add them up. I am just going to copy this downward, make it a little bit simpler. Now of course, what's that referring to? That's the hourly people. What if we want to know the average salary of the hourly people? Instead of SUMIF we used AVERAGEIF. The average salary of these hourly people here is 28,000. And once again, in both cases of course, we could be looking at different criteria, but the functions play out in similar ways.
I actually use AVERAGEIF a lot more than SUMIF. AVERAGEIF is relatively new; it was introduced in Excel 2007. It has been around for a while. So these are good functions for tabulating information typically out of a database kind of list of information. COUNTIF, SUMIF, and AVERAGEIF.
- Referencing, copying, updating, and converting formulas
- Using the logical functions and creating compound logic tests
- Searching for and matching data based on specific criteria
- Reconfiguring cell data using text functions
- Calculating dates, times, and days of the week
- Analyzing mathematical and financial data
- Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
- Working with rounding functions
- Returning cell references