Ever stared at a messy Excel sheet wondering, "How many cells here actually contain text?" I've been there too. Last quarter while analyzing customer feedback data, I spent half an hour manually counting comments before realizing there's a much smarter way. Getting accurate counts of text cells changes everything when you're dealing with inventories, survey responses, or any dataset mixing numbers and words.
Why Counting Text Cells Matters in Real Work
People usually search for excel formula for counting cells with text when they're:
- Cleaning up imported data (those annoying "#N/A" values everywhere)
- Validating survey forms where text responses are mandatory
- Trying to separate numeric entries from descriptive entries
- Tracking incomplete records in databases
What most tutorials don't tell you? Blank cells pretending to be text (like invisible spaces) will ruin your count. I learned this the hard way during a budget report fiasco last year.
The Core Formula Breakdown
COUNTIF: The Workhorse Function
This is where most folks start:
The asterisk acts like a wildcard saying "anything with characters." Simple, right? But here's where it gets messy:
- Counts cells with numbers stored as text (like '00123)
- Ignores genuine blanks but counts cells with empty strings ("")
- Includes cells with only spaces (which look empty but aren't)
I use this for quick scans but never for critical reports anymore after it double-counted some phantom entries.
When You Need Precision: ISTEXT + SUMPRODUCT
Want to exclude numbers disguised as text? Combine functions:
How it works:
- ISTEXT checks each cell: TRUE for real text, FALSE otherwise
- The double hyphen (--) converts TRUE to 1 and FALSE to 0
- SUMPRODUCT adds up all the 1s
This saved me during client data audit when COUNTIF included product codes like "12345X".
=IFERROR(SUMPRODUCT(--ISTEXT(range)), "Check data errors")
Special Cases You'll Definitely Encounter
Ignoring Numbers Stored as Text
That time HR sent me employee IDs formatted as text? Nightmare. Use:
Ugly but effective. List all digits (0-9) to exclude cells containing numbers. Pain to write, but accurate.
Counting Cells with Specific Text
Need to count how many support tickets say "Urgent"?
Add asterisks for partial matches:
The Blank vs Empty String Trap
Here's what most get wrong: cells with formula-generated "" appear empty but aren't. To exclude both blanks AND empty strings:
Formula Comparison Table
Which formula should you use?
| Formula | Best For | Limitations | Calculation Speed |
|---|---|---|---|
| =COUNTIF(range,"*") | Quick scans, non-critical data | Counts numbers-as-text, spaces | Fast |
| =SUMPRODUCT(--ISTEXT(range)) | Audits, financial data | Slower with huge datasets | Medium |
| =COUNTA(range)-COUNT(range) | When all non-numbers must count | Fails with boolean/logical values | Fast |
| =SUMPRODUCT((range<>"")*ISTEXT(range)) | Data cleaning, critical reports | Complex syntax | Slow |
Top Mistakes That Break Your Formulas
Over 10 years, I've seen these errors repeatedly:
- Quoting ranges: =COUNTIF("A1:A10", "*") // WRONG
=COUNTIF(A1:A10, "*") // CORRECT - Missing wildcards: =COUNTIF(A1:A10, "") // Counts empty cells!
- Case sensitivity: COUNTIF ignores case. Need case-sensitive count? Use:
=SUMPRODUCT(--(EXACT(range,"TARGET")))
When Formulas Aren't Enough: Alternative Tools
For datasets over 100K rows, formulas crawl. Try:
- Power Query: Import data > Filter column > Remove numbers > Count rows
- Ctrl+Shift+L: Apply filters, select text-only cells, check status bar count
- VBA Macro: (Use if you do this daily)
Function CountText(rng As Range) As Long
Dim cell As Range
For Each cell In rng
If VarType(cell.Value) = vbString Then CountText = CountText + 1
Next cell
End Function
Honestly? Unless you're an Excel wizard, Power Query is safer than VBA.
Real-World Use Cases
- Inventory sheets: Count description fields vs blank SKUs
- Survey analysis: Track open-ended response rates
- Data migration: Validate text field transfers between systems
- Attendance trackers: Flag days with "Sick" or "Leave" notes
FAQs: What People Actually Ask
Does COUNTIF count cells with spaces as text?
Yes, and it's infuriating. A cell with just spacebar hits looks empty but gets counted. Use =SUMPRODUCT((TRIM(range)<>"")*ISTEXT(range)) to exclude them.
Can I count colored text cells without VBA?
Sadly no. Excel formulas can't detect font color. You'd need VBA or manual filtering.
Why does =COUNTIF return zero when I see text?
Three likely culprits:
1. Numbers formatted as text (use ISTEXT to verify)
2. Leading/trailing spaces (apply TRIM)
3. The range reference is broken
How to count text across multiple sheets?
Try: =SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!range"), "*"))
Where "sheets" is a named range containing sheet names. Messy but works.
Advanced Tactics for Power Users
When basic text counts aren't enough:
=SUM(1/COUNTIF(range, range)) // Array formula (Ctrl+Shift+Enter)
Text cells meeting date criteria:
=SUMPRODUCT(--ISTEXT(A1:A100)*(B1:B100>DATE(2023,1,1)))
Ignore formula blanks:
=SUMPRODUCT(--(range<>"")--ISBLANK(range))
Warning: Array formulas can crash large datasets. Test on copies first.
My Personal Workflow
Here's how I approach text counts after years of Excel battles:
- Check data size > 20K rows? Use Power Query
- Need accuracy? =SUMPRODUCT(--ISTEXT(range))
- Quick check? =COUNTIF(range,"*") with manual space check
- Always verify with filter + status bar count
Remember: No single excel formula for counting cells with text works perfectly in all scenarios. Test multiple methods against your actual data.
Leave a Comments