Go Back

Count cells that are not blank

Excel allows a user to count all cells that contain any value, by using the COUNTA function.  The function takes in count texts, numbers, spaces, results of other function, etc. This step by step tutorial will assist all levels of Excel users in counting non-blank cells in a range.

Figure 1. The result of the COUNTA function

Syntax of the COUNTA Formula

The generic formula for the COUNTA function is:

=COUNTA(range)

The parameter of the COUNTA function is:

  • range – a range of cells where we want to count non-blank cells.

Counting Non-Blank Cells Using the COUNTA Function

In our example, we want to count all non-blank cells in the range B3:B10. In the cell B8, we put a space. In the cell B9, there is the formula which returns a space as a result.

The formula looks like:

=COUNTA(B3:B10)

The parameter range is B3:B10, while in the cell D3 we want to get a result of the COUNTA function.

To apply the COUNTA function, we need to follow these steps:

  • Select cell D3 and click on it
  • Insert the formula: =COUNTA(B3:B10)
  • Press enter

Figure 2. Using the COUNTA function to count non-blank cells in the range

There are 5 names in the range and also two spaces. Because of that, the result of the function in the cell D3 is 7.

Ignoring Empty Strings When Using the COUNTA Function

The COUNTA function takes in the count all cells that are not blank. This includes spaces and empty strings returned by other function, which can’t be seen in the range. If we want to ignore these empty strings, we can use the SUMPRODUCT function instead. We will show how it works in the same example. The formula looks like:

=SUMPRODUCT(--(LEN(B3:B10) > 0))

Figure 3. Counting non-blank cells with the SUMPRODUCT function

The function checks all cells in the range if their length is greater than 0. In the end, it counts all cells which are greater than 0. As you can see, the result of the function is 5.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Add cells in columns A & B that are not blank
Solved by Z. E. in 19 mins
i need to adjust a formula to not count blank cells
Solved by Z. J. in 13 mins
I want to count how many rows have not had any action taken yet but are not yet Overdue. So my row G from cell G3 to G102 need to be blank and cell F3 to F102 need to be blank. I don't want it to count any rows that are blank (which naturally have the G and F columns blank. I want to count how many cells have F and G columns blank but still have text in column B which means that there is a task in that row. I've tried a few different formulas but they won't work.
Solved by I. H. in 29 mins

Leave a Comment

avatar