Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
A category of functions that's extremely valuable when working with large lists is the COUNTIF family of functions. There is a COUNTIF, a SUMIF and AVERAGEIF and three more sophisticated variations that allow you to use multiple criteria. In this particular list here we might want to tabulate how many Kitchen items we have. It's a large list, 300 rows or so and we don't necessarily want to sort the Data but we do want to know for example, how many of our products are in the Kitchen category, Kitchen Department, Bedroom Department, Dining Department, so on and we have various entries here.
Now to make this easier to see I'm going to hide a few columns here, columns C, D, E as I drag across these into, let's just say those three columns we'll hide for the moment, Right-Click and Hide. We've got a list here of all the different Departments that's been set up ahead of time, so let's zoom in a bit on this list, we'll use the Zoom Slider bar on the lower right-hand corner to zoom in a bit. So let's imagine, we want to tabulate how many kitchen items do we have here? And the function to use is COUNTIF; make the column just a tad wider as we watch this.
=countif, this function requires two arguments or parameters. Where are we looking for these entries here? We're looking in column B comma and what are we looking for? The word Kitchen. Now if we don't have this in a nearby cell, we'll type "Kitchen", but since it's right here to the left we'll just click that cell and press Ctrl+Enter since we don't want the active cell to move and we have an answer, it's 45. By Double-Clicking here, we'll copy this down the column.
Out of all the items that we have here, some 300 items, we've got 41 Bath entries; we see the formula there, 12 Entryway items and so on. In all examples here we're looking in column B simply counting how often that text appears. A companion function called SUMIF allows us to tabulate data. Where is the revenue coming from in our list of sales over here? This time what we'd like to be able to do is to focus for example on the Kitchen items and then go into the Revenue column to figure out how much money we're making here.
Here too I'll make it for the moment a bit wider. The function is called sumif. Where are we looking? We're looking in column B, so it starts off the same way, comma what are we looking for? All of the Kitchen entries, comma and when we find them where do we want to go? In the column H. Now if you are using this function where you have specific cell references for example, we could have highlighted cell B7 down to B310 or whatever, the length is here.
Just make sure that in situations like that that the number of cells that you select here matches the number of cells that you get here. And if we use column references like in this example, we don't worry about that. But what we're saying in effect is, every time the entry in column B is Kitchen, then go into column H and grab that information and keep doing that over and over and tabulate the total, because it's a SUMIF. So there is the Revenue for Kitchen items, Double-Click to copy this down and we could see very quickly, Bedroom and Dining Room and also Living Room as three top items revenue-wise as we look at the data here.
The SUMIF function, where are we looking, what are we looking for, and then having found that, which numerical field do we want to add, in this case Revenue. And as you might expect, AVERRAGEIF is going to work pretty much the same way, this time it's doing little bit differently though, because we want to look at the average cost of something. So to make this a little easier to see, I'm going to make one of the columns, let's say column A, a bit narrower so we can see this better. So Average Cost/Item is going to be averageif.
Where are we looking? We're looking in column B again, comma what are we looking for, the entry in J7 namely Kitchen and when we find this what are we trying to do? We're trying to average the price, that's in column F. What's the average price of our kitchen items that we're currently selling? $26.30, how about the others? Probably no surprise that Living Room is the highest. Bath is pretty low, isn't it, compared to the others.
So all we're doing in this case is saying in effect we're looking column B and when the entry is equal to what we see in column J, then go into column F instead of adding them as we did in column L or simply averaging these entries this time from column F so these three functions; COUNTIF, SUMIF, AVERAGEIF give us great tabulating capability when working with database type lists.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64741 Viewers
80 Video lessons · 124337 Viewers
52 Video lessons · 60269 Viewers
59 Video lessons · 46100 Viewers