Using COUNT Functions

Using COUNT Functions

Count functions are really useful, and are among my favorite functions that Excel has to offer – and I wanted to share a few of them with you here, to give you a sense of how they work so you can apply them to your data more easily.

So, the name says it all. COUNT – and its several variations – counts the number of and values within any range of cells you designate. It can do everything from a straight-up tally of how many cells in a given range contain numbers to telling you how many cells in a given range contain a very specific letter, word, phrase, or number.

We’re going to look at 4 COUNT functions in this video:

COUNT

COUNTA

COUNTBLANK

---

COUNTIF

First, let’s look at COUNT. This is a really simple function, consisting of an equal sign, the word COUNT, and within parentheses, a range of cells. That range can be a range of contiguous cells, a series of ranges, or a series of individual cells that you click on while holding the CTRL key.

As long as the addresses are housed within that set of parentheses, you’ll get a count – of how many of those cells contain numbers. The numbers can be simple integers, dollar amounts, dates, or times – any numeric content at all.

So here in cell E18 in the Sales & Commissions worksheet, I’ll insert the COUNT function and count how many quarterly sales totals are included. If you know you have 100 salesreps and there are 4 quarters in the year, that number should be 400 – if it’s lower, someone’s numbers haven’t been entered. I’m using a smaller number of salesreps here so you can see the entire range that’s being counted. This is not a sum of the sales, mind you – it’s just the count of numeric values that have been entered in the range.

We see in the result that we have just 36 quarterly sales numbers, so there’s data missing – and I could now go find out what happened to it and then add that missing content to complete my worksheet.

Moving on, COUNTA counts cells that aren’t blank – so cells containing text or any non-numeric content, as well as cells containing numbers, are counted.

And now, switching to our US Population worksheet, I’ll click in cell C332, I’ll insert the COUNTA function to count how many US cities are included in this worksheet’s data. In case any of them don’t have numbers, using the city names is the best way to find out how many records there are, regardless of the completion of the statistical data.

---

COUNTBLANK does the opposite – instead of counting how many cells contain data, it counts how many don’t. And here in cell D332, I’ll use COUNTBLANK to tell me if any of the City’s 2020 population data is missing.

And finally, COUNTIF is the most powerful of the bunch – instead of just one argument – the range – it has 2 – the range and the criteria to look for in that range.

With this function, I can find out how many cities in New York are in the list by using New York as the criteria argument in the function – note that the criteria is in quotation marks, to tell Excel that’s what to look for. I’ll put this in cell C331, and type:

=COUNTIF(C5:C330, “New York”)

The answer is 5. If I change the Criteria to any other state, it will count the occurrences of that state’s name, too. When I double-click the formula and change New York to Pennsylvania, we see the result change to 3.

Hopefully, even if COUNT functions aren’t your new favorite, you’ll find lots of uses for them in your worksheets!