Let’s take a look at an example to see how COUNT could be useful. Our example worksheet contains a short set of survey results.
Question #2 asked respondents to rate their satisfaction with a product.
Most people responded with numbers, but a few responded with text. Let’s use COUNT to figure out how many people submitted numerical responses.
First, click into cell F2 and type “Number of numerical ratings Q2:” so we don’t forget what we used this function for.
Next, click into G3 type “=COUNT(” to start the formula:
Then click and drag to select the values of column C. Don’t include the header cell, or it will be included in the count.
In this case, including the header cell won’t make a difference.
But it could in future COUNTs, so we’ll start getting in the habit now.
Finally, close off the parentheses and hit Enter.
It doesn’t get much simpler than that!
Let’s get some more information from our spreadsheet. Question #4 also asked respondents to rate their satisfaction.
How would you go about counting the number of numerical responses in both of these combined?
Again, click into a new cell; we’ll use G5. Use the same process as above; type “=COUNT(“, then select the values in column B.
Instead of closing off the formula here, though, type a comma and select the values in column D.
Now close off the parentheses and hit Enter.
You now have the number of numerical cells in both columns.
If you want to count cells that contain any type of data, instead of just numbers, you’ll need COUNT’s sister function, COUNTA.
This is even simpler than COUNT. It returns the number of non-empty cells in a range or multiple ranges.
If you use COUNTA on column B in the example spreadsheet, you’ll see that it counts everything, and gives you a total count of 50.
Column D, however, contains two blank cells and will result in a COUNTA of 48.
Because counting numerical and non-empty cells is a common task, Excel has made it easy for you.
Highlight one of the columns in the spreadsheet, and look at the status bar near the bottom-right corner of the Excel window:
You’ll see the count displayed in the status bar, and if you’ve highlighted numerical data, you’ll also see the average and the sum.
The count displayed here is the equivalent of COUNTA; it counts all non-empty cells.
But what if you want to only count numerical values? You can do that too.
Just right-click on the status bar, and select Numerical Count.
Now you’ll see the numerical count (the equivalent of the COUNT function) displayed as well.
Adding power with COUNTIF
What if you don’t want to count everything, though?
If you want to count a specific subset of cells, COUNTIF will help you out. (If you’re familiar with AVERAGE and AVERAGEIF, COUNTIF does the same type of thing.)
This function lets you specify a specific criterion, and it will count the number of cells that meet it.
For example, let’s look at column B, Gender. You might want to get a count of the number of Ms and Fs in this column. COUNTIF can help.
Here’s the syntax: