Using the COUNTIF family of functions

- In this worksheet called COUNTIF, we're seeing a list of our products in column C. Quick Look seems to indicate that there are some duplicates, but we've got different sizes, based on what we're seeing in column D, also different colors. And for the year here, let's say, we're half year or quarter, it's not indicated. We've got the Price of each Item, how many were sold, and our total Revenue. And off to the left, each Item, we can see, is in a different Category. There are different Categories, different Departments here. Over in column J, we want to track how many Kitchen Items we've sold.

So looking at the list over on the left hand side, you can begin to imagine. We see Kitchen in column B here and there. We want our totals just for Kitchen. Now, in order to make this easier to see for you, I'm going to hide some of the columns and then zoom in. So I'm going to hide columns D and E. We don't really need to see them. Drag across the top, right-click, and Hide. And using the slider bar in the lower right hand corner, I'll click the plus a few times, we'll move in here. How many Kitchen Items do we have? Now, off to the left we want to be sure for the moment we're seeing column B.

Makes it easier to write formulas. We need a count of the Items here. The COUNTIF function is what we want to use, =COUNTIF. It's as if we're saying, how many Kitchen Items do we have? COUNTIF. Now, we're not, for the moment, focusing on Price or anything like that, simply Items that we have. COUNTIF says, where are we looking? We're looking in column B. Now, we can either click column B, because there's nothing else in there, except the word Department and some others. Doesn't make any difference really, what else is there.

We're simply looking for the word Kitchen. So following our designation as to where we're looking, we put in a comma, and then we're going to click on cell J4, which contains the word Kitchen. If we didn't have this nearby, we could type "Kitchen". And as I press Enter here, we see that there are 45 items. And I can simply copy this down the column here. And now we have the total of the other Items as well, 73 Bedroom Items, 40 Kitchen Items, 54 Living Room, and so on.

Now, if we want to track the Revenue, we can't use the COUNTIF function. It simply counts. But a companion function called SUMIF says, well, let's say it starts off from the same idea. Let's see where we have those Kitchen entries, and then go over and pick up the Revenue from column H. So SUMIF has three parameters, three arguments, we might say. Start with the equal sign, =SUMIF). Like before, we're going to click on column B, comma, and then again use J4, because we want to find the Kitchen Items, but when we do find them, then we want to move over into column H.

Now, in other kinds of scenarios, where you've indicated, maybe, cell B2 or B5, down to B300 or whatever, you want to make sure that the area that you're trying to gather the totals from is a companionary in terms of the way you've put in the address. In other words, if you refer to a column here, refer to a column over here on the right. Or if this says B2:B300, this should say on the right H2:H300, if that were the locations. So we're done here. The Revenue total for our Kitchen Items is this, 2,000,000.

Drag this down the column, we've got the others as well too. And we can even do this with Average Cost for Item, because there's a function called AVERAGEIF, and in order to see this, I'll make column C a bit narrower for the moment. We don't have to see all that, exactly. Same general idea here, but now it's AVERAGEIF. And it too starts off like SUMIF. Once again, we're looking in column B, because we're focusing on the Kitchen Items. Comma, now we'll refer to cell J4, Kitchen Items, comma, but now we want to average the Average Cost per Item, and that's in column F.

Those are the costs of each Item. What's the Average Cost per Item? And for Kitchen, it's relatively low. Some of the others are going to be higher, as you might imagine. Drag this down here. So these are valuable tools. Here are the Averages in each Department. Looks like Living Room, and you could imagine couches as being quite expensive, so the average item, Bath is the lowest on the list. We get good, quick, valuable information. Once again, function here, AVERAGEIF. Looking in column B, but only for Kitchen Items, let's average all those Items based on the data in column F, and certainly all the others here as well, same idea here, Living Room and so on.

So these are powerful tools, the SUMIF family of functions, SUMIF, COUNTIF, AVERAGEIF.