# How to Count Cells with Text in Excel

HomeFormulasHow to Count Cells with Text in Excel

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 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 that cells with text mean all the cells where you don’t have numbers, blank cells or errors.

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

So let’s get started.

## 6 Ways to Count Cells with Text in Excel in Different Situations

As I said, all these 6 methods can be used in different situations.

1. COUNTIF + Wildcard
2. SUMPRODUCT + ISTEXT
3. Count Specific Text Cells
4. Counting number of Cells with a Partial Text
5. Count Case Sensitive Text
6. VBA to Count Cells with Text

…let’s explore each and every method and learn when we can use them and how they work.

### 1. Combo Formula using COUNTIF with Wildcard Characters to Count Cell with Text Values

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). The formula will be:

=COUNTIF(A2:A20,”*”) 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.

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,”<> “)

### 2. Using 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 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.

### 3. Formula to Count Cells with Specific Text

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.

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

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

#### …here’s 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 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.

### 5. VBA to Count Cells with Text

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:

Related: Excel VBA Guide – Getting Started with Writing MACRO Codes in Excel

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

## 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 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?

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.

Related Tips

### 14 thoughts

1. Hi,

If i would like to count the quantity of text if found certain of text at front and back. example:

(TOP:C55.BOT:C53,C54,C56,C98,C99)

How to count the qty of text for Top and bot

Ans: Top=1 , Bot=5

2. I want to count the number of cells, both blank and with data, that remain in a range after the first data entry.
I have a set range of cells where the user enters data. Say the user starts halfway down the column to enter data. I need to count the number of cells starting with the first cell containing data to the end of the range, including all blank, text, data and errors:
[ _ _ _ _ 2 _ 1 _ _ e _ T _ 3 _ _ ] – there are 12 cells after the place where the first data (2) was entered.

3. Can you help me on the error in this formula. =IF(ISNUMBER(\$AH15),ANDIF(\$AH15>150,(” High Random Blood Sugar “&\$AH15&” Mg.%. “, “”)&” “&IF(\$AH15>150,”Urine Sugar “&\$AI15&”. “, “”),(“”)
AH15 is Number or Text “ND” i.e. Not Done.

Thank you.

Prakash Kulkarni

4. first 5 examples alco can be programed via vba, eg.
Sub Test_GetCountTextCells()
If TypeName(Selection) = “Range” Then
MsgBox GetCountTextCells(Selection)
End If
End Sub
Function GetCountTextCells(vrng)
On Error Resume Next
GetCountTextCells = Application.WorksheetFunction.CountIf(vrng, “*”)
End Function

5. Hi,
first 5 ways also can bi used via vba…, eg:

Sub Test_GetCountTextCells()
If TypeName(Selection) = “Range” Then
MsgBox GetCountTextCells(Selection)
End If
End Sub
Function GetCountTextCells(vrng)
On Error Resume Next
GetCountTextCells = Application.WorksheetFunction.CountIf(vrng, “*”)
End Function

6. Hi,

I want say cell a = Leeds and cell c = sheffield, cell f = the mileage between these two cities. Cell c is a drop down with lots of different cities so the sum of f will always be the different distance which is pre determined. So this is the scenario. If cell a = Leeds and cell c = sheffield the sum in cell f = 40. If cell c changes to manchester the predetermined figure for this distance in cell f = 55 (example). Hope you can help.

7. You may explain other count formulae like dcount, counta, etc., I have few clarifications in them. I am sure you will cover mine too since you don’t leave anything unattended.

Thanks a lot for your efforts. You are my EXCELlent Guru!. Keep it up.

8. i want the formula to find out the count of cells making sum of 90 and above

• You simply need to use SUMIF for this.

9. Nice tutorial. Sumproduct function with exact does not work even if there is a space after the word. This needs to be noted. Anyway, thanks for the tutorial.

10. Thank you.

11. =counta(range)-count(range)
counta-counts all
count-counts numbers