How to Count Cells That Contain Text in Excel

Why This Task Matters in Excel

Every analyst, project manager, finance officer, or small-business owner eventually needs to sift through columns of mixed information—numbers, dates, error values, and plain words. Being able to quickly identify how many cells contain text is a deceptively simple task with enormous practical impact.

Imagine a customer-feedback worksheet in which responses are stored numerically (1–5) if the customer selected a rating, or as free-form comments if they provided additional notes. Before you begin sentiment mining you first need to know how many comments were actually written. In another department, HR might track employee certifications: a date in a cell indicates a completed course, whereas a blank or the word “Pending” means training is still outstanding. Counting text-based cells instantly tells HR how many employees still need to act.

These examples stretch across industries: sales teams monitor “Won”, “Lost”, or “In Negotiation”; manufacturing plants record “Pass” or “Fail”; logistics firms mark shipments as “Delivered”, “On Route”, or “Delayed”. Regardless of field, counting the text entries supports dashboards, KPIs, and resource planning.

Excel shines at this problem for two reasons. First, its built-in worksheet functions handle pattern matching, data typing, and logical evaluation without the overhead of SQL or scripting language connectors. Second, it allows one-click recalculation: any time your source data changes, the text count updates instantly—crucial for live dashboards and daily operations. Not knowing how to do this typically forces teams into slow manual reviews or error-prone copy-pasting into filtered lists, wasting hours and eroding confidence in the results.

Mastering text-counting also builds foundational skills. You will learn how Excel treats cell data types, how wildcards work, what constitutes an “empty” cell, and how array math enables sophisticated, dynamic formulas. These concepts feed directly into more advanced tasks such as validation, conditional formatting, dataset cleansing, and building automated reports.

Best Excel Approach

The single most efficient method for counting cells that contain text in a contiguous range is the COUNTIF function with the asterisk wildcard \"*\".

=COUNTIF(range,"*")

Why is this the best first choice?

• COUNTIF is straightforward: one range, one criterion.
• The asterisk wildcard matches “any sequence of characters”, so any non-blank text entry—“A”, “Completed”, “A003-X”, or “$\", yes even a single space—passes the test.
• It ignores purely numeric cells, dates, logical TRUE/FALSE, and errors, giving you an immediate text-only tally.
• The function is available in every Excel version since 1997, so workbooks remain fully backward compatible.

Use this method when:
– You do not need to exclude cells that contain formulas returning an empty string.
– The range is on the same worksheet or workbook (COUNTIF cannot reference closed workbooks).
– Simplicity and clarity are top priorities for other users reading your formulas.

When might you choose an alternative? If you must differentiate between visible text and hidden empty strings, count across multiple criteria, or perform additional calculations in a single formula. For these cases SUMPRODUCT or the newer FILTER-plus-COUNTA combination may fit better.

Alternative syntax examples:

'Array/legacy approach (all versions):
=SUMPRODUCT(--ISTEXT(range))

'Dynamic array approach (Excel 365+):
=COUNTA(FILTER(range,ISTEXT(range)))

Parameters and Inputs

• range – Required. One- or multi-dimensional range of cells such as [A2:A100] or [B2:E500]. Must be a valid worksheet reference.
• Criteria (for COUNTIF) – The string or expression against which each cell is tested. For counting all text cells the criterion is literally \"*\" (without extra spaces).

Data preparation recommendations

1. Remove superfluous leading/trailing spaces unless they carry meaning; COUNTIF treats a cell containing only spaces as text.
2. Ensure numbers stored as text are genuinely intended to be text; \"00123\" counts as text, but 123 does not.
3. Convert error values if they should not be counted. COUNTIF ignores #N/A and similar errors, whereas ISTEXT treats them as non-text.
4. Avoid merged cells; they complicate range references.
5. Validate that any formula returning \"\" (empty string) is acceptable or must be excluded—empty strings are text, yet appear blank.

Edge-case handling

• Hidden rows/filtered views do not affect results—functions evaluate the underlying dataset, visible or not.
• Array constants like [\"A\",\"B\",\"C\"] inside formulas are allowed in Excel 365+, but older versions require CTRL+SHIFT+ENTER entry if using multi-cell arrays.
• External (closed) workbooks break COUNTIF; use SUMPRODUCT instead.

Step-by-Step Examples

Example 1: Basic Scenario – Survey Comments

Assume you conducted a customer satisfaction survey in which scores are stored in [B2:B21] and optional comments in [C2:C21]. You want to know how many participants left a comment.

1. Select any blank cell, say [E2].
2. Enter:

=COUNTIF(C2:C21,"*")

3. Press ENTER. The cell immediately shows the count of rows in [C2:C21] containing text.

Why it works: COUNTIF loops through each cell in [C2:C21]; if the cell matches the pattern \"*\" (at least one character of any type), it increments the internal counter. Numeric scores, blanks, or formulas returning \"\" fail the test and are skipped.

Expected result example: If 12 out of 20 respondents left comments, [E2] displays 12.

Common variations

• Separate comment counts by rating. For 5-star reviewers only, use:

=COUNTIFS(B2:B21,5,C2:C21,"*")

• Count comments that mention “delivery” (case-insensitive):

=COUNTIF(C2:C21,"*delivery*")

Troubleshooting tips
– If you expect 12 but see 13, inspect the extra row; it probably contains a space character or hidden non-printing character. Use TRIM or CLEAN to strip unwanted characters.
– If you see zero even though text is visible, ensure no additional leading apostrophes or your range reference is correct.

Example 2: Real-World Application – Project Status Dashboard

A project office maintains a master schedule with task numbers in column A, start dates in B, end dates in C, and status in D. Status can be: \"Not Started\", \"In Progress\", \"Complete\", or a blank if the task was canceled. Management needs a live dashboard summarizing the number of active text statuses per project manager.

Setup
– Data range: [A2:D500]
– Project manager assignment in column E
– Dashboard summary area beginning at [H2]

Step-by-step

1. Build a pivot table if you only need static snapshots. For a live formula, continue.
2. In [H1] list distinct project managers or create them via UNIQUE in Excel 365+:

=UNIQUE(E2:E500)

3. In [I2] (next to first manager), enter:

=COUNTIFS(E2:E500,$H2,D2:D500,"*")

4. Copy the formula down alongside the manager list.

Explanation
COUNTIFS evaluates two conditions: the manager match and the status cell containing text. Cells with a blank status are excluded, giving you a pure count of tasks that actively carry a descriptive state. Values update in real time for daily stand-ups without having to refresh or rebuild a pivot.

Integration with other features
– Conditional formatting can color the dashboard cell I2 red if the count exceeds a risk threshold.
– The counts feed into chart widgets for executive reporting.
– Named ranges enhance maintainability: define StatusRange as [D2:D500] to simplify the formula: =COUNTIFS(E2:E500,$H2,StatusRange,"*").

Performance considerations
On 20 000-row schedules, COUNTIFS remains efficient, recalculating almost instantly. If your workbook balloons past 200 000 rows, consider converting the data to an Excel Table and turning on manual calculation during bulk updates.

Example 3: Advanced Technique – Exclude Empty Strings and Formula Errors

You inherited a financial model where column G holds reconciliation notes. Some formulas intentionally return \"\" if no discrepancy is found. Visually the cell looks blank, but ISTEXT returns TRUE (\"\" is text). Management only wants to count real, manually entered notes.

Goal: Count cells containing text of length greater than zero.
Range: [G2:G5000]

1. In [J2] enter:

=SUMPRODUCT(--(LEN(G2:G5000)>0),--ISTEXT(G2:G5000))

2. Press ENTER (no array keystrokes required in modern Excel; in pre-2019 versions, SUMPRODUCT auto handles arrays).

Logic breakdown

• LEN(G2:G5000)>0 produces an array of TRUE/FALSE values where the string length exceeds zero. Formulas returning \"\" fail because length equals zero.
• ISTEXT verifies the cell’s data type is text (protects you from counting numbers or dates).
• The double unary operator (--) converts TRUE/FALSE to 1/0.
• SUMPRODUCT multiplies the two arrays element-wise then adds the results, returning the total count.

Professional tips
– Encapsulate the logic in a named formula like CountRealNotes for readability.
– Use dynamic references with Excel Tables: =SUMPRODUCT(--(LEN(Table1[Notes])>0),--ISTEXT(Table1[Notes])).
– Combine this with FILTER to list the actual note cells:

=FILTER(G2:G5000,(LEN(G2:G5000)>0)*(ISTEXT(G2:G5000)))

so auditors can review the source rows.

Edge-case management

• If users paste text with a trailing space, LEN evaluates to greater than zero, count includes it—consistent with your definition of “contains text”.
• If formulas return \"OK\", they are counted. Replace formula output with NA() if you prefer to skip.

Tips and Best Practices

1. Store data in Excel Tables; structured references (e.g., Table1[Status]) make formulas self-adjusting when rows are added.
2. Use helper columns for complex criteria rather than nesting deep functions; maintenance becomes easier and performance improves.
3. Apply TRIM and CLEAN to imported data before counting to eliminate non-printing characters that can inflate counts.
4. Document your criteria in adjacent cells—placing \"*\" in [Z1] then using =COUNTIF(range,$Z$1) clarifies intent for future users.
5. When dealing with millions of rows, turn off automatic calculation during large pastes to prevent sluggishness; recalc once after all data is in.
6. Pair COUNTIF counts with conditional formatting to visually verify which cells qualify, catching unexpected inclusions quickly.

Common Mistakes to Avoid

1. Relying on COUNTA instead of COUNTIF
   – COUNTA counts numbers, errors, boolean values, and empty strings, leading to inflated results. Use COUNTIF with \"*\" for true text counts.
2. Overlooking empty strings
   – Formulas that output \"\" appear blank but pass the \"*\" test. Incorporate LEN greater than 0 or ignore formulas via COUNTIFS(range,"?*"), which requires at least one visible character.
3. Forgetting wildcard escaping
   – To count cells that literally contain an asterisk, you must escape it: ~*. Omitting the tilde gives totally different results.
4. Breaking links to external workbooks
   – COUNTIF cannot evaluate closed files; your formula silently returns #VALUE!. Replace with SUMPRODUCT or open the source workbook.
5. Mixing data types inadvertently
   – Importing a CSV might convert purchase order numbers to text. Decide upfront whether “00123” counts as text or numeric and standardize formatting accordingly.

Alternative Methods

When to migrate
– If workbook must open in Excel 2013, stay with COUNTIF or SUMPRODUCT.
– For dashboards requiring spill arrays or interactive lists, move to FILTER.
– Performance issues on multi-hundred-thousand-row models may push you toward Power Query and DAX, which handle data types explicitly and scale better.

FAQ

When should I use this approach?

Any time you need a live tally of descriptive values—status labels, commentary cells, categorical tags—COUNTIF with \"*\" is the quickest route. It excels in cleaned, well-structured datasets where empty strings are not a concern.

Can this work across multiple sheets?

Yes. Use 3-D references or sum multiple COUNTIFs:

=COUNTIF(Sheet1!A2:A100,"*")+COUNTIF(Sheet2!A2:A100,"*")

If you have dozens of sheets, wrap a SUMPRODUCT around INDIRECT, but note INDIRECT is volatile and increases recalculation time.

What are the limitations?

COUNTIF cannot touch closed workbooks, and it considers \"\" as text. Additionally, COUNTIF accepts only one criterion, so complex logic (e.g., text AND date before 2022) requires COUNTIFS, SUMPRODUCT, or helper columns.

How do I handle errors?

If your range contains #DIV/0! or #N/A values, COUNTIF ignores them, but ISTEXT also returns FALSE, so they do not inflate counts. To trap errors from faulty references, nest formulas inside IFERROR or use AGGREGATE functions designed to skip errors.

Does this work in older Excel versions?

Yes. COUNTIF and SUMPRODUCT date back to the 1990s. The only area where version matters is the dynamic array functions (FILTER, UNIQUE) available only in Excel 365 and Excel 2021.

What about performance with large datasets?

COUNTIF and SUMPRODUCT are reasonably efficient until you exceed hundreds of thousands of rows. At that point:
– Convert ranges to Excel Tables to minimize full-column references.
– Disable automatic calculation when loading data.
– Consider Power Query for pre-processing and Power Pivot (DAX) measures for counts; they are memory-optimized and multi-threaded.

Conclusion

Counting cells that contain text is a foundational Excel skill that unlocks rapid data profiling, quality control, and dynamic reporting. Whether you lean on the simplicity of COUNTIF, the flexibility of SUMPRODUCT, or the power of dynamic arrays, mastering these techniques enriches every other analytics task you undertake. Practice the examples on your own datasets, experiment with wildcards, and integrate counts into dashboards. As you grow comfortable, explore Power Query and DAX to extend these concepts to enterprise-scale models. Your future self—and your stakeholders—will thank you for the clarity and speed this capability delivers.