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”.
Leave a Comment