How to Count Non-Blank Cells in Excel

Excel cells can house different data types, and sometimes you need to get the count of a specific data type in your Excel spreadsheet. Thanks to Excel's arsenal of built-in functions, you can count any data type in your spreadsheets with Excel formulas.

Although it might seem a bit pointless to someone who isn't in your shoes, there may be a scenario where you need to count the non-blank cells in your spreadsheet. So how do you count all the cells that contain any kind of value? Excel has just the right function for you.

Using the COUNTA Function in Excel

COUNTA is a built-in Excel function that looks through the values you feed to it and counts the number of values that aren't blank. The syntax for COUNTA in Excel is as below:

        =COUNTA(value1, value2, ...)

You can insert the values directly or refer to a cell or a range that contains them. You can input multiple values for COUNTA to look through.

COUNTA is straightforward to use as it does only one thing and takes in a single argument. However, COUNTA isn't very flexible about what it considers blank and what it considers data. As an example, a single space in a cell will qualify it as a cell containing data, and as a result, it will be counted by COUNTA.

Here are some things to consider when using COUNTA:

Empty strings such as " " do not qualify as blank and are still counted by COUNTA.
Cells displaying errors do not qualify as blank and are counted by COUNTA.
The number zero (0) in both its forms does not qualify as blank and is counted by COUNTA.
Cells that are made to look blank through custom formatting in Excel do not qualify as blank and are counted by COUNTA.

Basically, any sort of value or information in a cell makes it a non-blank cell in the eyes of COUNTA. If you're looking for something more specific, you can use COUNTIF and COUNTIFS to add your own conditions. This way, you can specifically tell your formula what to look for and count.

With all that said, if COUNTA is the function you're looking for, then let's move on to learning how you can use COUNTA to get a count of non-blank cells in Excel.

How to Count Non-Blank Cells in Excel With the COUNTA Function

The different scenarios where COUNTA can be useful all boil down to a smaller scenario where you need to count non-blank cells. As an example, in this sample spreadsheet, we have a table containing the names of some employees and the supervisor's comments on each employee.

The goal here is to count how many of the employees have been written a comment by the supervisor. Rather than counting the blank cells one by one, we're going to kindly ask COUNTA to do it for us.

In this example, the supervisor comments that we want COUNTA to look through are placed in cells B2 to B10. Hence, we're going to have COUNTA look through that range. Here's how:

Select the cell where you want to display the formula output.
Go to the formula bar and enter the formula below:
```
        =COUNTA(B2:B10)
    
```
Press Enter.

Once you press Enter on your keyboard, COUNTA will instantly return a number. In our case, this number was the number of employees who had received a comment from the supervisor. This is because those who did not receive a comment had an empty cell in front of them and were not included in the COUNTA count.

Count Non-Blank Cells in Excel With Ease

Excel comes packed with an arsenal of count functions intended for different scenarios where you need to count specific cells. One such function is COUNTA which provides you with the number of cells that contain any type of value or data in a range.

COUNTA is truly selective in its judgment, as any cell that contains any sort of value (even a blank space) is qualified as a non-blank cell. You can combine COUNTA with other Excel functions to create formulas that seem like magic. Now that you know all about COUNTA, why not go ahead and give it a try?