Grasp the power, utility, and application of Excel's COUNT functions in careers and classes that demand data handling and analysis. Learn to use COUNT, COUNTA, COUNTBLANK, and COUNTIF functions to effectively manage and analyze large databases.
Key Insights
- Excel's COUNT functions are powerful tools for counting the number of values within a designated range of cells.
- The COUNT function gives a tally of how many cells in the given range contain numbers, inclusive of integers, dollar amounts, dates, or times.
- COUNTA function counts cells that aren't blank, including those containing text or any non-numeric content as well as cells containing numbers.
- The COUNTBLANK function essentially does the opposite, counting how many cells in the designated range do not contain data.
- COUNTIF is the most versatile of the COUNT functions, allowing for two arguments: the range and the specific criteria to look for within that range.
- Understanding and effectively using these COUNT functions can greatly optimize data management and analysis tasks in Excel-based work.
COUNT Function Family
-
COUNT
Counts cells with numbers. Skips text and blanks.
-
COUNTA
Counts all non-empty cells regardless of content.
-
COUNTBLANK
Counts empty cells in a range.
-
COUNTIF / COUNTIFS
Counts cells meeting one or multiple conditions.
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!
Video Transcription:
Full Video Transcript
Count functions are really useful and are among my favorite functions that Excel has to offer. 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. The name says it all—the COUNT function and its several variations counts the number of 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 four count functions in this video: COUNT, COUNTA, COUNTBLANK, and COUNTIF.
The COUNT Function
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 separated by commas, or a series of individual cells also separated by commas that you click on while holding down the Control key. As long as the addresses are housed within that set of parentheses, you'll get the 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 and Commissions worksheet, I'll insert the COUNT function and count how many quarterly sales totals are included. If you know you have 100 sales reps and there are four quarters in the year, that number should be 400. If it's lower, somebody's numbers haven't been entered.
I'm using a smaller number of sales reps 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 into the range. We see in the result that we have just 36 quarterly sales numbers, so there's data missing. I could now go find out what happened to it and then add the missing content to complete my worksheet.
The COUNTA Function
Moving on, COUNTA counts the cells that aren't blank. So cells containing text or any non-numeric content, as well as cells containing numbers, are counted. I'm going to switch over to our USA Population worksheet and click in cell C332. I'll insert the COUNTA function to count how many U.S. 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. And our result is 326.
The COUNTBLANK Function
Now COUNTBLANK does the opposite. Instead of counting how many cells contain data, it counts how many don't. Here in cell D332, I'll use COUNTBLANK to tell me if any of the city's 2020 population data is missing. We see one of them is missing, and it happens to be on screen right here for Lynn, Massachusetts.
The COUNTIF Function
Finally, COUNTIF is the most powerful of the bunch. Instead of using just one argument (the range), it has two: 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 that's what to look for.
I'll put this in cell C331 and type equals COUNTIF, provide the range which is cells C5 through C330, and then after the comma, the words "New York" in quotes. After I close the parentheses and press Enter, I get my result—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.
Practical Applications of Count Functions
So the count functions have a lot of uses and give you the ability to see what's in your worksheet, how many times something appears, what's missing, and how much data you actually have stored. Hopefully, even if they don't end up being your favorites, now you're going to find lots of uses for counting your data.