Go Back

How to Use COUNTA to Count Non-Blank Cells

Read time: 30 minutes

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.

Notes

The function takes in the count all cells that are not blank. This includes spaces, which can’t be seen in the range.

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

Ive attached an Excel file I use. Ive filled all non-blank cells with red. The numbers in column A are the number of blank cells from column B to the last non-blank cell. Im wondering if theres a way to get Excel to calculate this number for me automatically. The trick is that I dont want to count the total number blank cells in the row, just the number up to the non-blank cell. If Excel could tell the me the column header of the first assignment skipped(or the last two or three skipped) as well that would be great. Thanks.
Solved by E. C. in 14 mins
I have a formula that counts non blank cells in Cell A2 now i want to create another array formula where in i want to use this count as one of the reference i.e. $F$1:$F$(value in cell A2). Please advice?
Solved by B. C. in 19 mins
a) In cell C2, write a formula to count the total number of sales leads. Use the COUNTA function.
Solved by C. A. in 25 mins

Leave a Comment

avatar