How To Count Cells With Text in Excel

Did you know that Microsoft Excel can count cells containing text?

You may need to count cells containing text when sorting through survey responses, tracking inventory, or cleaning data. Excel’s mix of numbers, blanks, and text can make this seemingly simple task a spreadsheet nightmare. Hence, you should know how to exclude numbers or cells with only spaces.

In this guide, you’ll learn easy methods to count cells with text in Excel.

To count cells with text in Excel:

• Use =COUNTIF(range, "*") for a quick count (includes numbers formatted as text).

• Use =SUMPRODUCT(--ISTEXT(range)) for strict text-only counts.

• To exclude blanks and spaces, try =COUNTIFS(range, "*", range, "<> ").

These formulas adapt to most scenarios in under 60 seconds.

Counting All Cells Containing Text

You need to count how many cells contain any kind of text, regardless of the type. This is particularly useful when handling survey responses, product lists, or any dataset where text entries matter more than numbers.

Excel provides a simple way to count text cells using the COUNTIF function. The formula for this is:

=COUNTIF(range, "*")

Explaining the formula:

• range: This defines the range of cells where Excel will search for text. You can adjust it based on your dataset.

• "*": The asterisk (*) is a wildcard character in Excel that represents any sequence of characters, meaning it matches any text string.

• Excludes numbers and blank cells: The formula only counts text-based entries, skipping numeric values, empty cells, and errors.

Example:

Let's assume you have text, numbers, and blanks in column A. You can count cells that contain text with the following formula: =COUNTIF(A1:A10, "*")

The number of cells with text in the range is 6.

Note: If some cells appear empty but still get counted, they might contain spaces. The COUNTIF function will also count cells containing numbers formatted as text values.

Counting Excel Cells with Text and Excluding Spaces and Empty Strings

Things aren’t always so straightforward when it comes to counting text in Excel. Some cells may appear empty but actually contain hidden characters like spaces, empty strings, or even line breaks. These invisible culprits can cause your formula to count cells that seem blank, leading to inaccurate results and unnecessary confusion.

To count cells with text in Excel while ignoring empty strings and spaces, you can combine Excel functions like SUMPRODUCT, ISTEXT, and TRIM. Here's how:

=SUMPRODUCT(--(TRIM(range)<>""), --ISTEXT(range))

Explaining the formula:

• TRIM(range): Removes leading, trailing, and extra spaces between words in each cell.

• TRIM(range)<>"": Checks if the cell is not empty after trimming spaces.

• ISTEXT(range): Verifies if the cell contains a true text value, returning TRUE for text and FALSE for numbers, errors, or blank cells.

• --: Converts TRUE/FALSE to 1/0.

• SUMPRODUCT function: Sums the true and false values for the final count.

Example:

If you want to count cells with text in the range A1:A10 while ignoring empty strings and space characters, use:

=SUMPRODUCT(--(TRIM(A1:A10)<>""), --ISTEXT(A1:A10))

The number of cells with text remains six as the formula ignores the empty strings (row 6) and spaces (row 8).

Counting Cells with Specific Text

Sometimes, you want to count only the cells that contain a specific word or phrase. The COUNTIF formula can help you achieve this. You simply have to specify the text as the criterion:

=COUNTIF(range, "specific_text")

Example:

To count cells in A1:A10 that contain the word "Bag," use:

=COUNTIF(A1:A10, "Bag")

Additionally, you can use this formula for partial matches with minor adjustments:

• "Sales*" counts cells starting with "Sales."

• "*2025" counts cells ending with "2025."

• "*project*" counts cells containing the word "project" anywhere.

Counting Cells with Text While Excluding Specific Characters

If you need to exclude certain entries, you can combine formulas to refine your count. Using the COUNTIF function twice allows you to count cells that contain text but exclude those that contain specific characters.

=COUNTIF(range, "*") - COUNTIF(range, "*characters*")

Here, the first part counts all cells with text, while the second part subtracts cells containing the characters.

Example:

Let's assume you want to count all cells with text in our example except those containing "Bag":

=COUNTIF(A1:A10, "*") - COUNTIF(A1:A10, "*Bag*")

The range contains five cells with text that don't include "Bag."

Final Thoughts on "How To Count Cells With Text in Excel"

You can count cells with text using Excel functions like COUNTIF and SUMPRODUCT. We have explained four methods to perform this task to make your life easier. Start with the basics and experiment with advanced formulas to adapt to different scenarios.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ on "How To Count Cells With Text in Excel"

1. Why does COUNTIF count some blank cells?

COUNTIF may count blank-looking cells if they contain spaces or invisible characters. To fix this, use =COUNTIFS(range, "*", range, "<> ") to exclude spaces. Alternatively, apply TRIM(range) to clean the data before counting.

2. How can I count text cells while ignoring numbers formatted as text?

Use =SUMPRODUCT(--ISTEXT(range)) instead of COUNTIF(range, "*"). This ensures that only actual text values are counted, excluding numbers stored as text. If your dataset contains mixed formats, consider converting numbers to true numerical values before counting.

3. Can I count multiple specific words in one formula?

Yes! Use =SUM(COUNTIF(range, {"word1", "word2", "word3"})) to count multiple words at once. This formula counts the occurrences of each word and sums them up. Use wildcards like "*word*" inside the array if you need partial matches.

Related Articles

How to Remove Hyperlinks in Excel

How to Indent in Microsoft Excel

How to Export Power BI Data to Excel