Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Multiple key sorting
- Single and multiple column numeric filters
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX
Skill Level Appropriate for all
The SUMIF/COUNTIF family of functions, introduced in Excel 97, now include four new functions in Excel 2007 and they give you lots of tools for tabulating a variety of statistical totals when you have database type lists. The functions and questions are listed in Column J here. Normally you wouldn't see these listed just like this, and I've separated them because they're more or less in groupings here. SUMIF, COUNTIF and AVERAGEIF. AVERAGEIF is new in Excel 2007.
And these functions you can almost figure out what they do by their names. This list here is substantial. It might grow, it might shrink. And we might want to know, for example, simple question, how many half-time people do we have, how many full-time people do we have? So start off actually with COUNTIF and use this function right here, =COUNTIF. Two arguments. Where are we looking? Now you certainly can and I certainly could here highlight all of the cells in Column D, but it's a lot simpler to simply click Column D instead.
So instead of doing all that let's just click. And what's great about this capability too, or this approach to the function is that if we add names to the list or if we take them out we don't need to change the way this formula is being set up. So using an entire column here makes sense. Where are we looking? Column D. What are we looking for? Full Time. Now if I don't have that phrase nearby in a cell, although I do to the right here, I'll simply put in double- quote and type in "Full Time". And we are all done, Enter.
How many full-time people do we have here? 393. Obviously we're going to keep that, adjust the display of the format. If we do have the data nearby, possibly instead you would just click on that cell. So how many full-time people do we have? And if we somehow found that to be valuable and we have the other kinds of statuses listed next to this we could clean up the display a little bit by decreasing the decimals and simply drag this down a few more cells, there we go.
And so we've got a Half Time count and an Hourly count and a Contract. So COUNTIF is pretty straightforward and easy to use, and it does provide us with good information about our data. And if you want something like this to be setup along with your data it's not a bad idea to put off to the right. But with one slight exception it might not be so good. If you do a lot of filtering, which of course does hide rows, some of this data may be hidden at different times. Now the other option possible is to put this above your data, but then it makes these kinds of formulas not quite as easy to write, because you couldn't be able to use entire columns there.
All right, without going through all the variations here but just a few more, the SUMIF function does go a step further and it allows us to tabulate some data from another range. And when we are using different ranges here they must be parallel. So let's say that instead of counting how many people that we have that are full-time, we want to know their total salaries. So SUMIF has a third argument, comma, and in this example it's Column G so we'll just G, all done.
Total salaries of our full-time people. There too we could copy that down if wanted the information for the others. And initially these two functions were around for quite a while, a lot of clamoring for this function, new in Excel 2007, and you can imagine what it does. Real simply quickly here we'll just simply put in-- we want to know the average salary of for example, the people that are full time, and there it is, and we could do the same for the others as well too, so AVERAGEIF.
In prior versions before Excel 2007, if you wanted to use multiple criteria you had to use array formulas but these three new functions here are just that they are exactly nearer the others except they end in S. I'll start with this one. And I am going to point out here that actually SUMIFs and AVERAGEIFs are little different than COUNTIFs and start with this idea. We need to add up the salaries, not only the people who are full time, but the people who are full time and have a certain job rating.
And we can have multiple sets of criteria. So the SUMIFs function begins with what it is we are trying to add up. So the order of things here might get a little confusing if you just remember what we've done in the previous ones. In the other functions that we just saw the data to be tabulated came last, not comes first. So we want to be tabulating salaries, comma, and now in pairs various criteria. And we can have one pair, two pair, three pair, quite a few in fact.
The first pair might be Status, comma, Full Time. That's in cell L2 so I'll just refer to that. We could hit Enter right here and simply get total salary of our full time people. We already did that with SUMIF, but to expand on the idea here, SUMIFs allows us multiple criteria, so what else are we looking for here? People who also have a job rating, comma and because this is a number we don't have to put it in quotes, those who have a job rating of 5.
We could have another pair. So the first pair indicates we want to be looking at those who are full-time and, even though we don't see the word and, second pairing here is job rating of 5. We press Enter. The total salaries of our full yime people who have a job rating of 5. It might be more interesting and more valuable to be able to do this as an actual average. So why not just pull this down here and readjust it, put it next to average, make a change? We want to know the average salary of all those people in our list here who are full time.
So we've got a pairing here who are full time and who have a job rating of 5. There is the average salary for that group. Now again, there could be following the five, a comma, a pair of criteria, two more of these, another pair, another pair, on and on and on and on, without long cumbersome formulas. So these are great tools for tabulating data, particularly adept at handling information across lists. SUMIFs, COUNTIFs, and AVERAGEIFs.
If you want more examples you might also want to check out the course on Advanced Formulas and Functions either for Excel 2007 or Excel 2010. They are identical in the two different versions.