How-To

How to Count Cells With Text in Excel

By Ben Stockton

Published March 15, 2023

Counting cells with text in Excel can be useful for data analysis. Follow the steps in this guide to help you quickly identify relevant data in your spreadsheets.

Do you need to count how many cells in a range contain text? Maybe you want to know how many cells have specific text or characters. Perhaps you want to exclude numbers and formulas from your count.

Whatever your reason, Excel has several functions that can help you count cells with text in your spreadsheet. In this article, we’ll show you how to use them.

Use COUNTIF With Wildcards

The easiest way to count cells with any text is to use the COUNTIF function with wildcards as search arguments for your formula. Using the asterisk (*) as a wildcard, you can match any character sequence and count any cells in your spreadsheet that include any text strings.

Here’s an example. If you want to count how many cells in the range B2 to B20 have text, you can use this formula:

=COUNTIF(B2:B20,"*")

This will return the number 4 because, in this hypothetical scenario, four cells in the range have text values.

You can also use COUNTIF with other wildcards. For instance, you could use the question mark (?), which matches any single character, or the tilde (~), which escapes a wildcard character.

Use COUNTA Minus COUNT

Another way to count cells with text is to use the COUNTA function minus the COUNT function.

The COUNTA function counts all non-empty cells in a range, while the COUNT function counts only numeric values. By subtracting one from the other, you get the number of cells that contain non-numeric values, such as text or errors.

For example, if you want to count how many cells in the range B2 to B20 include text or errors, you can use this formula:

=COUNTA(B2:B20)-COUNT(B2:B20)

This will return 6, because six non-empty cells in this hypothetical range aren’t numeric.

Use SUMPRODUCT with ISTEXT

Another way to count cells with text is to use a formula that uses the SUMPRODUCT function with the ISTEXT function.

The ISTEXT function returns TRUE for cells that contain text and FALSE for others. The SUMPRODUCT function then adds these logical values as numbers (TRUE = 1 and FALSE = 0).

For example, if you want to count how many cells in the range B2 to B20 that only contain text (excluding errors), you can use this formula:

=SUMPRODUCT(--ISTEXT(B2:B20))

This will return 6 because six cells in the range contain text only in this scenario. This allows you to use SUMPRODUCT with the nested ISTEXT function to identify the cells that contain text.

If a numerical value was included in this cell range, SUMPRODUCT would ignore it.

Better Data Analysis in Excel

You can quickly count cells with text in your Excel spreadsheet using the steps above. You can choose whichever one suits your situation best, but they should all achieve the same effect. One interesting thing you can do with text cells in Excel is to change the title case to make text upper or lower case.

If you're new to Excel, you might want to learn top Excel tips to supercharge your spreadsheets. You should also consider learning some Excel keyboard shortcuts to help speed up your productivity.

Want to clean up your spreadsheet? You can find and delete blank rows in Excel to tidy up your data. You can also search for duplicates in Excel, allowing you to remove any extra data causing errors in your calculations.