How to count number of cells with text in Excel

Excel is a very powerful software when it comes to performing data analysis, mathematical, statistical or financial calculations. It comes up with a lot of functions which help us in doing the daily office tasks. It also has a wide variety of functions that deal with text data. We can manipulate the text data in a lot of ways using those functions.

Sometimes when you are working with big datasets, you need to know the exact number of cells that have any text data in them. So, in today’s tutorial we are going to learn how to count the number of cells that have only text.

In the data set shown above we can see that we have 28 cells in total and some of them have numeric data and some of them have text data. 

The method to find the number of cells with text data will remain the same if we want to find the number of cells within one column or whole data range. We will use the method COUNTIF with a special criterion to count the cells with text only.

Syntax of COUNTIF

The basic syntax of COUNTIF function is as follows;

COUNTIF(range, criteria)

range: 

It represents the data range in which we wish to count the number of cells.

criteria: 

This is the parameter where we’ll define the condition to check against and count the cells. We’ll use a wildcard i.e., * to count the cells with text in them. It will count all the cells with texts and if there is numeric data stored as text, that will also be counted as text. However, #N/A and Boolean values are not counted as texts.

We’ll count the cells with text in one column and then in the whole data range one by one. 

Method 1: Count text cells inside one column only

We’ll count the number of cells having text in the Items column for this example. We can see that there are 7 rows in total in this column and there are only 4 cells with apparent text. However, the number 2100 was formatted as text and TRUE, #N/A are not counted as texts as explained earlier. Therefore, we should get a count of 5 as a correct result. Let’s do this by following the step below.

Step 1 – Create the formula to count text cells in one column

• We’ll use the COUNTIF formula to count the number of cells with text in the Items column first. The following formula will be used;

=COUNTIF(C3:C9,”*”)

C3:C9 here represents the column of items in which we wish to count the number of text cells.

Method 2: Count text cells inside the whole data range.

We’ll count the number of cells having text in the whole data range now. We can see that there are 4 columns in total and there is a whole column i.e.Sales with numeric values which will not be counted. The number 2100 was formatted as text and TRUE, #N/A will not be counted as texts as explained earlier. Therefore, we should get a count of 19 as a correct result. Let’s do this by following the step below.

Step 1 – Create the formula to count text cells in whole data range

• We’ll use the COUNTIF formula to count the number of cells with text in the Items column first. The following formula will be used;

=COUNTIF(B3:E9,”*”)

B3:E9 here represents the whole data range in which we wish to count the number of text cells.