How to Count Cells with Text in Excel (Formula)

- Written by Puneet

Last week one of my readers asked me for a simple formula that he can use to count cells with text. Actually, he was trying to figure out which is the best method to use.

But if you think for a second. In Excel, you have different formulas to count all the cells where you have text values. But the point is which one you should use.

I believe that it depends on the situation. One thing which you need to understand here is that cells with text mean all the cells where you don’t have numbers, blank cells, or errors.

Today in this post, I’d like to share 6 different ways to count cells with text in different situations.

1. COUNTIF with Wildcard Characters to Count Cell with Text Values

The best formula to count cells with text is COUNTIF with a wildcard character (* – Asterisk). In the below data table, you have a list of names and mobile numbers in a single column. And from this list, you need to count the number of cells with names (text). The formula will be:

=COUNTIF(A2:A20,"*")
count-cells-with-text-using-countif-and-astrisk-min

When you enter it, it will return the count of the cells with text only.

Here’s How This Formula Works

Here we have used COUNTIF to count cells with specific criteria and for criteria, we have used an asterisk. When you use an asterisk it will count cells with any number of characters other than logical values, numbers (if they are not entered as text), and errors.

Note: If there are some cells where you just have a blank space, will be counted as text. So for this, you can amend your formula like this.

=COUNTIFS(A2:A20,"*",A2:A20,"<> ")

2. SUMPRODUCT and ISTEXT to Count Number of Cells with Text

To count the number of cells with text in them you can also create a formula by combining SUMPRODUCT and ISTEXT. And the formula will be:

=SUMPRODUCT(--ISTEXT(A2:A20))

When you enter it, it will return the number of cells that contain text.

Here’s How This Formula Works

To understand this formula, you need to split it into three different parts.

First, you have used ISTEXT to check if a cell contains text or not. Here ISTEXT returns an array where for all the cells in which you have TRUE for the cells with text and FALSE for all other cells.

Second, you have added a double minus sign to convert the TRUE/FALSE array into 1/0. Now, for all the cells with the text, you have 1 and 0 for all others.

Third, you have used SUMPRODUCT to sum that array. And that sum is the number of cells that have text in them.

Using SUMPRODUCT here means you don’t need to enter your formulas as an array.

Read Also – How to Count Colored Cells in Excel

3. Formula to Count Cells with Specific Text

Now, let’s dig deeper a little bit. Suppose you need to count the cells where you have a specific text. Here is an example. In the below data table, you need to count the cell where you have “John”. And for this, the formula will be:

=COUNTIF(A2:A20,"John")

This formula will return the number of cells where the text you have specified is.

4. Cells with a Partial Text

It happens sometimes when you need to count cells by matching a partial text. And, COUNTIF and wildcard characters can help you with this.

Check out the below table where we have the product name and invoice numbers combined. And from this, you need to count the number of cells with “Product-A”. Use the below formula:

=COUNTIF(A2:A20,"Product-A*")

This formula will return the number of cells where you have “Product-A”.

5. Case Sensitive Text

Now here is a different thing. You need to count the cells with a specific text and that should be a case-sensitive count.

From the below table, you need to count the cells with the name “JOHN” but here you have the same name in the small case letters but the count should be for capital letters.

For this, you can combine SUMPRODUCT with EXACT.

=SUMPRODUCT(--EXACT("JOHN",A1:A20))

It will return the number of cells where you have “JOHN”.

How this Formula Works

To understand this formula, you need to split it into three different parts.

First, you have used EXACT to compare the entire range of cells with the text “JOHN”. It returns an array where you have TRUE for the cells where “JOHN”, ignoring “john”, “John”, and all other cells.

Second, you have added a double minus sign to convert that TRUE/FALSE array into 1/0. Now, for all the cells with the “JOHN”, you have 1 and 0 for all others.

count-cells-with-text-exact-one-zero-array-min

Third, you have used SUMPRODUCT to sum that array. And that sum is the number of cells that have “JOHN” in them.

5. VBA to Count Cells with Text

Using a macro code is a big time saver. The below codes will help you to count the number of cells with a text in the selection and in the entire worksheet.

For selection:

Sub countTextSelection() 
Dim rng As Range 
Dim i As Integer 
For Each rng In Selection 
If Application.WorksheetFunction.IsText(rng) Then 
i = i + 1 
End If 
Next rng 
MsgBox i
End Sub

Entire Worksheet:

Sub countTextWorksheet() 
Dim rng As Range 
Dim i As Integer 
For Each rng In ActiveSheet.UsedRange 
If Application.WorksheetFunction.IsText(rng) Then 
i = i + 1 
End If 
Next rng 
MsgBox i 
End Sub

Related: What is VBA in Excel

Conclusion

I am sure all these methods are sufficient for your to count cells with text in any given situation. You can use any of the methods which you think are perfect for you.

For most situations, the first method is applicable without any problem. But, if you want to go specific you can use other methods. For me, VBA is a big time saver as I don’t have to apply formulas. Now it’s your time to tell me something.

Do you have any other method which we can use to count the number of cells with the text?

Make sure to share your views with me in the comment section, I’d love to hear from you. And please, don’t forget to share this post with your friends, I am sure they will appreciate it.

Get the Excel File

Leave a Comment