How to Count Cells with Text in Excel

Last updated: August 18, 2026

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 for counting text in Excel, from a one-line wildcard count to multi-condition, case-sensitive and unique-value counts.

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 what you actually need.

COUNT vs COUNTA vs COUNTIF vs COUNTIFS vs COUNTBLANK: which one applies?

Most counting problems in Excel are not formula problems. They are function-choice problems. Five functions share the word COUNT and each one counts a different thing, so picking the wrong one gives you a number that looks plausible and is wrong.

Rule: COUNT counts numbers, COUNTA counts entries, COUNTIF counts matches. If your result is higher than you expected, you almost certainly reached for COUNTA when you wanted COUNTIF.

One trap worth knowing up front: COUNTA and COUNTBLANK disagree about formulas that return an empty string. A cell holding =IF(A1>5,"Yes","") looks blank, but COUNTA counts it as filled while COUNTBLANK also counts it as blank. Both are counting it, which is why the two totals can add up to more than the number of cells in your range.

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 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. Count the text cells with this 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.

Rule: =COUNTIF(range, "*") answers "how many cells hold text", not "how many cells hold real words". A ZIP code stored as text still counts.

Counting Excel Cells with Text and Excluding Spaces and Empty Strings

Things aren’t always so straightforward when 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:

To count the text in 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).

Rule: A cell containing one space is text to Excel. If your count is a few too high, the extra entries are almost always stray spaces, not real data.

Counting Cells with Specific Text

Sometimes you want to count only the cells holding a particular word or phrase. Pass the text itself 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.

Rule: =COUNTIF(A1:A10,"Bag") counts whole-cell matches only. If you want "Laptop Bag" included, you need "*Bag*".

How do Excel wildcards work, and where do they break?

Wildcards are the difference between counting exact values and counting anything that contains a value. Excel supports three of them in COUNTIF and COUNTIFS criteria.

Four gotchas cause nearly every wildcard bug:

1. Wildcards do not work on numbers. =COUNTIF(A1:A10,"*5*") will not find the numeric value 1500. Wildcards only match text, which is precisely why "*" is a reliable text-only test.
2. You must escape a literal asterisk or question mark. To count cells that literally contain a question mark, use =COUNTIF(A1:A10,"*~?*"). Without the tilde, the question mark is read as "any single character" and your count balloons.
3. The criteria string caps at 255 characters. Longer criteria return #VALUE!. If you are matching long strings, switch to SUMPRODUCT(--(ISNUMBER(SEARCH(...)))).
4. Wildcard matching is not case-sensitive. "*bag*" and "*BAG*" return the same number. See the case-sensitive section below for the fix.

Rule: The asterisk means "any run of characters", the question mark means "exactly one", and the tilde turns either back into a plain character.

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

Rule: Excel has no "count if not contains" criterion, so subtraction is the method. Count everything, then subtract the matches you do not want.

How do you count text with two or more conditions?

COUNTIF takes exactly one condition. The moment your question has an "and" in it, you need COUNTIFS, which accepts up to 127 range and criteria pairs and counts only the rows where every pair is satisfied.

=COUNTIFS(range1, criteria1, range2, criteria2)

Example:

Say column A holds task names and column B holds their status. To count tasks that contain the word "report" and are still marked "Open":

=COUNTIFS(A2:A100, "*report*", B2:B100, "Open")

Three variations worth keeping:

• Text but not blanks or spaces: =COUNTIFS(A2:A100, "*", A2:A100, "<> ")
• Text within a date window: =COUNTIFS(A2:A100, "*", C2:C100, ">="&DATE(2026,1,1))
• Either of two words (an OR count): =SUM(COUNTIF(A2:A100, {"*bag*","*case*"})), because COUNTIFS cannot express OR on its own.

Rule: COUNTIFS is AND, an array constant inside COUNTIF is OR. Every range in a COUNTIFS must be the same size or Excel returns #VALUE!.

How do you count text case-sensitively in Excel?

COUNTIF and COUNTIFS ignore capitalisation entirely, so "BAG", "Bag" and "bag" all land in the same bucket. That is fine for survey answers and wrong for SKUs, coupon codes and case-based identifiers.

The fix is EXACT, which compares two strings character by character including case, wrapped in SUMPRODUCT so it works across a whole range:

=SUMPRODUCT(--EXACT(range, "Bag"))

For a case-sensitive partial match, swap SEARCH (case-insensitive) for FIND (case-sensitive):

=SUMPRODUCT(--ISNUMBER(FIND("Bag", range)))

Rule: If capitalisation is meaningful in your data, COUNTIF is the wrong tool. EXACT for whole cells, FIND for partial matches.

How do you count unique text values in Excel?

Counting cells with text and counting distinct text values are different questions. A column with "Bag" five times has five text cells and one unique value.

In Microsoft 365 and Excel 2021 or later, the UNIQUE function makes this a one-liner:

=COUNTA(UNIQUE(A2:A100))

If a blank cell in the range is inflating the result by one, filter it out first:

=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))

On older versions without UNIQUE, the classic reciprocal-COUNTIF pattern still works. Each value contributes 1 divided by however many times it appears, so every distinct value sums to exactly 1:

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))

Rule: The &"" in the legacy formula is not optional. It forces blanks to be treated as empty strings and prevents a divide-by-zero error.

How do you count cells with text across multiple sheets?

COUNTIF does not accept a 3D reference. Typing =COUNTIF(Jan:Mar!A:A,"*") returns an error, which surprises people who expect it to work like SUM.

The simplest reliable approach is to add the sheets together explicitly:

=COUNTIF(Jan!A2:A100,"*") + COUNTIF(Feb!A2:A100,"*") + COUNTIF(Mar!A2:A100,"*")

For many sheets, list the sheet names in a helper column (say E2:E13) and let INDIRECT build the references:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&E2:E13&"'!A2:A100"), "*"))

Rule: INDIRECT is volatile and recalculates on every change, and it breaks if a sheet is renamed. On a large workbook, plain addition is faster and safer than the clever version.

How do you count characters instead of cells?

"Count cells with text" and "count characters" are often searched together but need different functions. LEN returns the character count of a single cell, including spaces:

=LEN(A1)

To total the characters across a range, wrap it in SUMPRODUCT:

=SUMPRODUCT(LEN(A1:A10))

To count how many times one character or word appears inside a cell, measure the length before and after removing it:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))

For a whole word rather than a single character, divide by the length of that word:

=(LEN(A1)-LEN(SUBSTITUTE(A1,"bag","")))/LEN("bag")

Rule: COUNTIF counts cells, LEN counts characters. If a cell contains "bag bag", COUNTIF scores it 1 and the SUBSTITUTE method scores it 2.

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 count numbers, dates, and text while ignoring 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 matters when you are counting a list of names that might also contain ID numbers you want included in the total.

Rule: COUNTA counts a formula that returns "" as filled. If a column of formulas gives you a suspiciously round COUNTA, that is why.

Why does your COUNTIF return 0?

A COUNTIF that returns 0 when you can plainly see matches on screen almost always comes down to one of six causes. Work through them in this order.

Rule: A COUNTIF that returns 0 is a data problem, not a formula problem, about nine times out of ten. Test with =COUNTIF(A:A,"*Bag*") first. If the wildcard version finds them, you have stray spaces.

Microsoft documents the full criteria syntax and its limits in the official COUNTIF function reference, which is worth a look if you hit an edge case this guide does not cover.

How to Count Cells with Text in Google Sheets

If you are working in the cloud, the process is nearly identical. The standard COUNTIF function works the same way in Google Sheets.

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

The differences show up in the advanced cases. Google Sheets adds COUNTUNIQUE, which Excel does not have, and handles ranges through ARRAYFORMULA and QUERY rather than Excel's dynamic arrays. For the Sheets-specific versions of everything on this page, see our full guide to how to count cells with text in Google Sheets.

Final Thoughts on Counting Cells With Text in Excel

Counting text in Excel comes down to picking the right function before you write anything. COUNTIF with a wildcard handles most cases, COUNTIFS handles conditions, SUMPRODUCT handles everything COUNTIF cannot. Start with the basics and reach for the advanced patterns only when the simple ones give you a number you do not trust. Counting cells with text is the foundation for an inventory tracker excel that flags items by category or reorder status. For a finance-side example of these counting techniques in action, the simple balance sheet template uses COUNTIF and COUNTA throughout its category totals.

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 Counting Cells With Text in Excel

1. How do you count names in Excel?

Names are text values, so use =COUNTIF(range, "*"). If your list mixes names with blank cells, this formula counts only the cells holding names. To count one specific name, use =COUNTIF(range, "John"), and to count distinct names use =COUNTA(UNIQUE(range)).

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?

Use the =COUNTA(range) function. This counts all cells that contain data, including numbers and text, while ignoring truly empty cells. Note that it also counts formulas returning an empty string.

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.

6. What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes a single range and a single condition. COUNTIFS takes up to 127 range and criteria pairs and counts a row only when every condition is true at the same time. Every range in a COUNTIFS must have identical dimensions or Excel returns #VALUE!.

7. How do I count cells that do not contain certain text?

Excel has no "does not contain" criterion for wildcards, so subtract instead: =COUNTIF(A1:A10,"*") - COUNTIF(A1:A10,"*Bag*"). For an exact non-match rather than a partial one, =COUNTIF(A1:A10,"<>Bag") works directly.

8. Can COUNTIF count across multiple sheets?

Not with a 3D reference. =COUNTIF(Jan:Mar!A:A,"*") returns an error. Add the sheets explicitly with =COUNTIF(Jan!A2:A100,"*") + COUNTIF(Feb!A2:A100,"*"), or use =SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"),"*")) where SheetList holds the sheet names.

9. How do I make COUNTIF case-sensitive?

You cannot. COUNTIF ignores capitalisation by design. Use =SUMPRODUCT(--EXACT(range,"Bag")) for a case-sensitive whole-cell count, or =SUMPRODUCT(--ISNUMBER(FIND("Bag",range))) for a case-sensitive partial match.

Related Articles

How to Count Cells with Text in Google Sheets

How to Remove Hyperlinks in Excel

How to Indent in Microsoft Excel

How to Export Power BI Data to Excel