Excel Productivity Guide

Useful Excel Tips | Keyboard Shortcuts | VBA Codes

**Download this Free E-Book **to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Last week one of my readers asked me for a simple formula which he can use to count cells with text.

Actually, he was trying to figure out which is the best methods he can use.

In Excel, we have different formulas which we can use to count all the cells where we have text values. But the point is which one we should use.

I believe that it depends on the situation.

One thing which you need to understand here that cells with text mean all the cells where you don’t have numbers, blank cells or errors.

So today, in this post, you will learn 6 different formulas which you can use to count the number of cells that contain text.

After reading this post, I want you to share with me that which formula is useful for you according to your need.

Let’s explore.

The best formulas to count cells with text is COUNTIF with 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 cells with names (text).

And, the formula will be:

=COUNTIF(A2:A20,"*")

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

Here we have used COUNTIF to count cells with specific criteria and for criteria we have used and asterisk.

So, 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.

Important 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,"<> ")

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.

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

First, you have used ISTEXT to check if a cell contains a 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 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 which have text in them.

Using SUMPRODUCT here has a benefit that you don’t need to enter your formulas as an array.

Now, let’s dig a deeper little bit. Suppose you need to count the cells where you have a specific text.

Here is the 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 cells where the text you have specified.

It happens sometimes when you need to count cells by matching a partial text. And, COUNTIF and wildcard characters can help you in 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 below formula:

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

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

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 below table, you need to count the cells with name “JOHN” but here you have the same name in the small case letters but count should be for capital letters.

For this, you can combine SUMPRODUCT with EXACT.

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

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

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 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.

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

Using a macro code is a big time saver. Below codes will help you to count the number of cells with a text in the selection and in 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

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

For most of the 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 the 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? Please share with me in the comment section. I would love to hear from you.

And, please don't forget to share it with your friends.

- Rafael Mejia
- Puneet Gogia