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 with 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 on how to count in excel when text is involved.

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.

Quick Reference: Excel Count Text Formulas

Use this table to find the right excel count formula for your data needs.

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 excel formula to count cells with text 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 excel count cells with 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.

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 in excel using 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 text contains logic 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 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."

Difference Between Count Text and Count Not Blank

A common confusion arises between "counting text" and "counting cells that are not empty." If your goal is to excel count if not blank (meaning you want to count numbers, dates, and text, but ignore empty cells), you should not use the wildcard method.

Instead, use the COUNTA function:

=COUNTA(range)

Key Differences:

• COUNTIF(range, "*"): Counts only text. Ignores numbers.
• COUNTA(range): Counts text, numbers, errors, and formulas that return empty strings.

This is vital when you are learning how to count names in excel where the list might accidentally contain ID numbers that you also want to verify.

How to Count Cells with Text in Google Sheets

If you are working in the cloud, the google sheets count cells with text process is nearly identical to Excel. The standard COUNTIF function works perfectly.

Formula: =COUNTIF(A1:A10, "*")

However, if you are working with dynamic arrays or need to count based on filtered lists in Google Sheets, you might combine this with the ARRAYFORMULA or SUBTOTAL functions depending on complexity. For 99% of cases, the asterisk wildcard method remains the standard for how to count text in excel and Google Sheets alike.

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

You can excel count number of cells with text using functions like COUNTIF and SUMPRODUCT. We have explained methods ranging from simple wildcard counts to complex exclusions. 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. How to count names in Excel?

To how to count names in excel, assuming the names are text values, use the formula =COUNTIF(Range, "*"). If your list contains names mixed with blank cells, this formula will count only the cells with names. If you have specific names to count (e.g., "John"), use =COUNTIF(Range, "John").

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

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

4. How do I count cells if they are not blank?

To excel count if not blank, use the =COUNTA(range) function. This counts all cells that contain data, including numbers and text, while ignoring truly empty cells.

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