< 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. 
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar