Go Back

Learn How to Apply a Formula for Counting Cells that are Not Blank

Sometimes you have a set of data in Excel that contains some blank cells. You can use the COUNTA function to create a formula that will only count the cells that are not blank.

COUNTA to Count Cells that are Not Blank

Let’s say you want to count cells that contain data, for example, the numbers of students in a data range. And you would like to ignore any cell that is blank. The way to do go about doing this is to use the COUNTA function.

The COUNTA function allows you to count cells containing any type of information. It can be error values and empty text (“”). If the range contains a formula that returns an empty string, this function counts that value. So the COUNTA function counts all cells that contain any type of “value” and does not count empty cells.

To count the total number of students in a range A1:A30 and add the result in A31 cell then select A31 cell and type :

=COUNTA(A1:A30)

And press Enter. And the answer will be 6.

Count Cells Containing Text Values

In case you need to count only cells containing any TEXT VALUES, so excluding cells dates and numbers, treating as blank cells and not including in the count value you can use this formula :

=COUNTIF (range, “*”)

The above formula counts only cells containing text values, so that means that cells with dates and numbers will not be included in the count but will be treated as blank cells.

In the generic form of the formula range is a range of cells, and “*” is a wildcard matching any number of text characters.

The formula for counting all non-blank cells in a range is (as COUNTA function above) :

=COUNTIF (range,”<>”&””)

This formula will correctly count all type of values – text, dates, and numbers, as you can see in the screenshot below.

Count Cells Containing Specific Text

To count the number of cells that contain specific text in a range you can use :

=COUNTIF(range,”*text*”)

So the result in cell F3, shows the counting value in range B2:B10 searching for criteria “i”.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

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

I need a formula that averages diagonal cells without counting the ones that are equal to zero
Solved by Z. E. in 13 mins
Add cells in columns A & B that are not blank
Solved by Z. E. in 19 mins
i need a formula for counting number but not include zero for counting.
Solved by O. J. in 23 mins

Leave a Comment

avatar