How to Count Cells with Text in Excel
Need to count how many cells contain text in your Excel spreadsheet? This guide covers the best formulas for counting text cells, including COUNTIF, COUNTA, and SUMPRODUCT methods for different scenarios.
Quick Answer
To count cells with text in Excel, use the formula =COUNTIF(A1:A10,"*") where A1:A10 is your data range. The asterisk (*) is a wildcard that matches any text.
For counting only text (excluding numbers), use =SUMPRODUCT(--ISTEXT(A1:A10))
Understanding the Key Functions
Excel offers several functions for counting cells, and choosing the right one depends on exactly what you want to count. Here is a quick overview of the main functions:
| Function | What It Counts | Best For |
|---|---|---|
| COUNT | Numbers only | Counting numeric values |
| COUNTA | All non-empty cells | Any filled cells (text, numbers, errors) |
| COUNTBLANK | Empty cells only | Finding gaps in data |
| COUNTIF | Cells matching criteria | Conditional counting |
The key insight is that COUNT only counts numbers, while COUNTA counts everything that is not blank. Neither function specifically targets text cells. For counting only text, you need either COUNTIF with a wildcard or SUMPRODUCT with ISTEXT.
Method 1: COUNTIF with Wildcard (Fastest)
The easiest way to count cells containing text in Excel is using COUNTIF with an asterisk (*) wildcard. The asterisk matches any sequence of characters, so it effectively counts all cells with any text.
Formula
=COUNTIF(range, "*")Example
=COUNTIF(A1:A100, "*")How It Works
- Select a blank cell where you want the result to appear.
- Type the formula =COUNTIF(A1:A100,"*") replacing A1:A100 with your actual data range.
- Press Enter. Excel displays the count of cells containing text.
Important: This formula also counts numbers stored as text and cells containing only spaces. If you need to count pure text values only, use Method 3 (SUMPRODUCT + ISTEXT) instead.
Method 2: COUNTA Function
COUNTA counts all non-empty cells in a range. This includes text, numbers, dates, and even error values. Use this when you want to count everything except blank cells.
Formula
=COUNTA(range)Example
=COUNTA(A1:A100)What COUNTA Counts
- Text values (letters, words, sentences)
- Numbers (including dates and times)
- Error values (#VALUE!, #REF!, etc.)
- Cells with formulas that return empty strings ("")
- Cells containing only spaces
What COUNTA Does NOT Count
- Truly empty cells (never had any content)
Tip: COUNTA is useful for quickly checking if any data exists in a range, but it is not specific to text. If your range contains a mix of text and numbers and you only want to count text, use Method 3.
Method 3: SUMPRODUCT + ISTEXT (Most Accurate)
For the most precise count of text cells only (excluding numbers, dates, and blanks), combine SUMPRODUCT with ISTEXT. This is the recommended method when you need to count strictly text values.
Formula
=SUMPRODUCT(--ISTEXT(range))Example
=SUMPRODUCT(--ISTEXT(A1:A100))How It Works
- ISTEXT(range) checks each cell and returns TRUE for text cells, FALSE for others.
- The double negative (--) converts TRUE to 1 and FALSE to 0.
- SUMPRODUCT adds up all the 1s, giving you the count of text cells.
What This Formula Counts
- Cells containing letters or words
- Numbers stored as text (prefixed with apostrophe)
- Cells containing spaces (considered text)
What It Excludes
- Numeric values (real numbers)
- Dates and times
- Boolean values (TRUE/FALSE)
- Error values
- Empty cells
Count Cells with Specific Text
To count cells that contain an exact word or phrase, use COUNTIF with the specific text as the criteria.
Exact Match Formula
=COUNTIF(range, "text")Example: Count cells containing exactly "Pending"
=COUNTIF(B2:B100, "Pending")Using a Cell Reference
If the text you want to count is stored in a cell, reference that cell instead of typing the text directly:
Cell Reference Formula
=COUNTIF(B2:B100, E1)Where E1 contains the text to search for
Count Multiple Words
To count occurrences of multiple different words at once, use SUM with an array of COUNTIF results:
=SUM(COUNTIF(A1:A100, {"Pending", "In Progress", "Complete"}))This formula counts cells containing "Pending", "In Progress", or "Complete" and returns the total.
Count Cells with Partial Text Matches
Wildcards in COUNTIF allow you to count cells where text appears anywhere within the cell content.
Wildcard Characters
| Wildcard | Matches | Example |
|---|---|---|
| * | Any sequence of characters | *apple* finds "pineapple" |
| ? | Any single character | c?t finds "cat" and "cut" |
| ~ | Escape character | ~* finds literal "*" |
Common Wildcard Patterns
Contains "apple" anywhere
=COUNTIF(A1:A100, "*apple*")Starts with "North"
=COUNTIF(A1:A100, "North*")Ends with "east"
=COUNTIF(A1:A100, "*east")Using cell reference with wildcard
=COUNTIF(A1:A100, "*"&E1&"*")Note: COUNTIF is not case-sensitive. "Apple", "APPLE", and "apple" are all treated as the same. For case-sensitive matching, use SUMPRODUCT with EXACT instead.
Exclude Blanks, Spaces, and Empty Strings
Sometimes cells appear empty but actually contain spaces or empty strings from formulas. Here is how to handle these edge cases.
Exclude Cells with Only Spaces
Use TRIM to ignore cells containing only whitespace:
=SUMPRODUCT(--(TRIM(A1:A100)<>""), --ISTEXT(A1:A100))Exclude Empty Strings from Formulas
When cells contain formulas that return empty strings (""), COUNTA still counts them. Use this formula to exclude them:
=SUMPRODUCT((A1:A100<>"")*1)Count Non-Blank Text Only
For the most thorough approach that counts only cells with actual visible text:
=SUMPRODUCT(--(LEN(TRIM(A1:A100))>0), --ISTEXT(A1:A100))Formula Comparison: Which One to Use?
| Scenario | Recommended Formula |
|---|---|
| Count all cells with any text | =COUNTIF(range,"*") |
| Count only text (exclude numbers) | =SUMPRODUCT(--ISTEXT(range)) |
| Count all non-empty cells | =COUNTA(range) |
| Count cells with specific text | =COUNTIF(range,"text") |
| Count cells containing a word | =COUNTIF(range,"*word*") |
| Count text, exclude spaces | =SUMPRODUCT(--(LEN(TRIM(range))>0)) |
| Case-sensitive count | =SUMPRODUCT((EXACT(range,"Text"))*1) |
Troubleshooting Common Issues
COUNTIF Returns Wrong Count?
- Hidden characters: Cells may contain invisible characters like leading/trailing spaces. Use TRIM in your formula.
- Numbers as text: COUNTIF("*") counts numbers stored as text. Use SUMPRODUCT(--ISTEXT(range)) for pure text.
- Empty string formulas: Cells with formulas returning "" are counted by COUNTA but not by COUNTIF("*").
COUNTIF Not Finding Text?
- Extra spaces: "Apple " and "Apple" are different. Use wildcards: "*Apple*".
- Text too long: COUNTIF cannot match text strings longer than 255 characters. Use SUMPRODUCT with SEARCH for long text.
Formula Returns #VALUE! Error?
- Invalid range: Check that your range reference is correct.
- Array formula needed: Some formulas require pressing Ctrl+Shift+Enter (legacy Excel). Modern Excel 365 handles arrays automatically.
Need Case-Sensitive Counting?
COUNTIF ignores case. For case-sensitive matching, use:
=SUMPRODUCT((EXACT(A1:A100,"Apple"))*1)Frequently Asked Questions
What is the formula to count cells with text in Excel?
The simplest formula is =COUNTIF(range,"*") where the asterisk (*) is a wildcard that matches any text. For example, =COUNTIF(A1:A10,"*") counts all cells containing text in the range A1 to A10.
What is the difference between COUNT and COUNTA in Excel?
COUNT only counts cells containing numbers. COUNTA counts all non-empty cells, including text, numbers, dates, and error values. Use COUNT for numbers only, COUNTA for any non-blank cells.
How do I count cells containing specific text in Excel?
Use =COUNTIF(range,"text") for an exact match. For partial matches (text appears anywhere in the cell), use =COUNTIF(range,"*text*") with asterisks as wildcards.
Does COUNTIF count cells with spaces?
Yes, COUNTIF with wildcard (*) counts cells containing spaces as text cells. To exclude cells with only spaces, use =SUMPRODUCT(--(TRIM(range)<>""), --ISTEXT(range)) instead.
How do I count only text cells and exclude numbers in Excel?
Use the formula =SUMPRODUCT(--ISTEXT(range)). This counts only cells containing text values and excludes numbers, dates, and blank cells.
Is COUNTIF case-sensitive when counting text?
No, COUNTIF is not case-sensitive. It treats "Apple", "APPLE", and "apple" as the same text. For case-sensitive counting, use =SUMPRODUCT((EXACT(range,"Text"))*1) instead.
Summary
Counting cells with text in Excel is straightforward once you know the right formula:
- =COUNTIF(range,"*") is the fastest method for counting any cells with text.
- =COUNTA(range) counts all non-empty cells (text, numbers, errors).
- =SUMPRODUCT(--ISTEXT(range)) is most accurate for counting only text values.
- Use wildcards (* and ?) for partial text matching in COUNTIF.
Choose your formula based on whether you need to count all text, specific text, or exclude numbers and blanks. For most everyday use cases, COUNTIF with the asterisk wildcard is the simplest and most effective choice.
Create Excel Tutorial Videos
Want to teach Excel formulas to your team? Deckoholic makes it easy to record screen tutorials with AI voiceover, zoom effects, and professional polish.
Try Deckoholic Free