How to Count Cells with Text in Excel

How to Count Cells with Text in Excel. Recently, one of my readers asked for a simple formula to count cells with text in Excel. There are various formulas available to count all cells containing text values, but the best one to use depends on the situation.

It’s important to note that cells with text include those without numbers, blank cells, or errors. In this post, I’ll share six different ways to count cells with text in different scenarios.

COUNTIF with Wildcard Characters

The best formula to count cells with text is COUNTIF with a wildcard character (* – Asterisk). For example, if you have a list of names and mobile numbers in a single column, you can use the formula:

=COUNTIF(A2:A20,”*”)

This formula counts only the cells with text. When using an asterisk as the criteria, COUNTIF will count cells with any number of characters, except for logical values, numbers (if they are not entered as text), and errors.

Note: If there are blank spaces within the cells, they will also be counted as text. To exclude these, use the formula:

=COUNTIFS(A2:A20,”*”,A2:A20,”<> “)

SUMPRODUCT and ISTEXT

To count the number of cells with text, you can also use the formula =SUMPRODUCT(–ISTEXT(A2:A20)). This formula checks if a cell contains text or not and returns an array with TRUE for cells with text and FALSE for all others.

The double minus sign is added to convert the TRUE/FALSE array into 1/0, where 1 represents cells with text and 0 represents all others. The SUMPRODUCT function then sums the array, resulting in the total number of cells that have text in them.

Using SUMPRODUCT has the advantage of not requiring the formula to be entered as an array.

Counting Cells with Specific Text in Excel

If you need to count cells that contain a specific text or partial text, there are several methods you can use in Excel. In this article, we will discuss some of the most common methods.

Using the COUNTIF Function

The COUNTIF function is a simple and straightforward method for counting cells with specific text. To use this function, you need to specify the range of cells you want to count and the text you are looking for. For example, to count the number of cells that contain the text “John” in the range A2:A20, you would use the following formula:

=COUNTIF(A2:A20,”John”)

This formula will return the number of cells that contain the text “John”.

Using Wildcard Characters

If you need to count cells with partial text, you can use the COUNTIF function along with wildcard characters. The asterisk (*) is a wildcard character that represents any number of characters. For example, to count the number of cells that contain the text “Product-A” in the range A2:A20, you would use the following formula:

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

This formula will return the number of cells that contain the text “Product-A”.

Using a Case-Sensitive Count

If you need to count cells with specific text in a case-sensitive manner, you can use the EXACT and SUMPRODUCT functions. The EXACT function compares two text strings and returns TRUE if they are exactly the same, and FALSE otherwise. The SUMPRODUCT function returns the sum of the product of corresponding elements in one or more arrays. To count the number of cells that contain the text “JOHN” in capital letters in the range A1:A20, you would use the following formula:

=SUMPRODUCT(–EXACT(“JOHN”,A1:A20))

This formula will return the number of cells that contain the text “JOHN” in capital letters.

Using VBA Macros

Using a VBA macro can be a time-saving method for counting cells with text. The following VBA codes will help you count the number of cells with text in either the selection or 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

For 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

Conclusion

These are some of the most common methods for counting cells with specific text or partial text in Excel. The method you choose will depend on your specific needs and preferences. For most situations, the COUNTIF function is sufficient, but if you need to be more specific or want to save time, the other methods may be more useful. Do you have any other methods to share for counting cells with text? Let us know in the comments below.