To count cells containing text in Excel, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. The asterisk is a wildcard: it matches text, while ordinary numeric values and truly empty cells are not counted.
The COUNTIF formula for text cells
In Microsoft Excel for Microsoft 365 on Windows, COUNTIF checks a range against one condition and returns how many cells meet it. Its basic structure is =COUNTIF(range,criteria). For a text count, the condition "*" means “any text string.” The quotation marks tell Excel that the asterisk is a criterion, rather than a multiplication symbol.
For example, with customer notes in A2:A100, enter =COUNTIF(A2:A100,"*") in an empty cell. If 37 cells in that range contain text, the result is 37. The count is not restricted to words: a cell containing a single letter, a sentence, or text that looks like a number can qualify.
Use a bounded range where practical. A reference such as A:A is convenient when the list will grow, but it asks Excel to evaluate the entire column. A range that covers the data, or an Excel table column, is often easier to review and can be more efficient in a large workbook.
What Excel treats as text
Excel stores values in different ways. Text is not the same thing as a number, a date value, or an empty cell, even when the cell’s display can make two of those things look alike. COUNTIF with the asterisk criterion is useful when the question is specifically “How many cells have text?” rather than “How many cells have something in them?”
- Ordinary words and sentences: counted as text.
- Numbers entered or imported as text: counted as text. For example, an entry such as 00123 may be stored as text to preserve its leading zeros.
- Numeric values and dates stored as Excel values: not counted by the text wildcard. Dates are generally stored as serial numbers and displayed with a date format.
- Truly empty cells: not counted.
- Cells containing a space: contain a text character, so they can be counted even though they look blank at a glance.
This distinction matters in imported lists. A column can contain a mixture of numeric IDs and IDs stored as text, perhaps because some have leading zeros or were imported from another system. The formula counts the text-stored entries, not every ID that looks like an ID on screen.
Enter the formula and check the range
To use the formula, select the cell where you want the total, type it, and press Enter. Then confirm that the reference covers the intended rows and does not include a heading or an unrelated note. For a small list in A2:A8, for instance, the formula would be =COUNTIF(A2:A8,"*"). If four of those seven cells contain text and the remaining three are genuinely empty or numeric, the result is 4.
When a list is an Excel table, a structured reference can make the formula easier to maintain as rows are added. With text entries in the table column named Status, use =COUNTIF(Table1[Status],"*"). This assumes the table is named Table1 and the relevant column is named Status. Excel typically adjusts table references as the table changes; check the actual table and column names in your workbook.
If the formula returns zero unexpectedly, first verify that the cells really contain text and that the range is correct. A value that looks like text may be a number formatted in a particular way, or your formula may point to a different set of rows than you intended.
Count only text that is not an empty string
A complication arises when cells contain formulas that display nothing. A formula such as =IF(B2=0,"",B2) can return an empty string. That cell is not physically empty: it contains a formula. Wildcard criteria can also behave in surprising ways with zero-length formula results, so do not rely on visual appearance alone when the distinction matters.
For a stricter count of text values whose displayed text has at least one character, use SUMPRODUCT with ISTEXT and LEN. With the values to check in A2:A100, enter =SUMPRODUCT(--ISTEXT(A2:A100),--(LEN(A2:A100)>0)). It counts text results with a positive length and excludes a formula result of an empty string. If the range contains the words Red and Blue, a formula returning an empty string, a number, and a truly empty cell, the result is 2.
This is useful for reports where formulas fill a column in advance but deliberately display blank until an input is entered. The formula also excludes a cell containing only spaces? No: a space is a character, so LEN is greater than zero. If spaces should be treated as empty too, trim the text before checking its length. With values in A2:A100, use =SUMPRODUCT(--ISTEXT(A2:A100),--(LEN(TRIM(A2:A100))>0)). This ignores leading and trailing ordinary spaces for the length test; it does not remove every possible invisible character, such as some nonbreaking spaces imported from web pages.
Count text that matches a particular pattern
The asterisk can do more than count every text entry. It can match text that starts with, ends with, or contains a chosen string. The criteria are not case-sensitive, so “North” and “north” match the same text condition.
- With entries in A2:A100,
=COUNTIF(A2:A100,"North*")counts text beginning with North, such as North Office. - With entries in A2:A100,
=COUNTIF(A2:A100,"*east*")counts text containing east anywhere, such as Southeast and East Wing. - With entries in A2:A100,
=COUNTIF(A2:A100,"????")counts text entries that match four characters. The question mark wildcard represents one character.
If the text you need to match includes an actual asterisk or question mark, put a tilde before that character in the criterion to treat it literally. For example, with product codes in A2:A100, =COUNTIF(A2:A100,"~*") counts text beginning with a literal asterisk. COUNTIF supports wildcards, but it does not provide a case-sensitive match; use a different approach if capitalization must distinguish results.
COUNTIF versus counting all nonblank cells
A common misconception is that COUNTIF with "*" counts every nonblank cell. It does not: the criterion is for text, so numbers are excluded. If the goal is to count cells that are not truly empty regardless of whether they hold text, numbers, or dates, a common formula is =COUNTA(A2:A100). With values in A2:A100, COUNTA counts cells containing any value, including text and numbers.
Another option is =COUNTIF(A2:A100,"<>"), which applies a not-equal-to-blank criterion. It is not interchangeable with the text-only wildcard. In particular, cells containing formulas that return an empty string can make “blank” counts confusing. For a reliable answer, decide whether you mean visibly nonempty, nonempty at the storage level, or text with at least one character, then choose the formula that reflects that definition.
Use COUNTIF with "*" when you mean text entries; use COUNTA when you mean cells containing any kind of value. If formulas returning empty strings are present, test a few known cells or use the LEN-based SUMPRODUCT formula for the specific nonempty-text definition. The right choice depends on what the count will be used to describe.
Common counting problems and fixes
- Cells look empty but are counted: inspect for spaces, formulas returning empty text, or other invisible characters. Select a suspicious cell and examine its formula bar. A LEN check can reveal text that is not obvious on the sheet.
- A number-looking entry is missed: it may be stored as a number, not text. If you need to count all populated IDs regardless of storage type, COUNTIF with an asterisk is the wrong test; consider COUNTA or standardize the column’s data.
- The result is too high: check whether the range includes a header, notes below the data, or cells containing spaces. Narrow the reference to the actual list.
- The result is too low: check for text in rows beyond the range, formulas that produce empty strings, or cells that are actually numeric. Imported data can mix types within one column.
- Formula displays as text instead of calculating: confirm the destination cell is not formatted as Text, remove any leading apostrophe, and re-enter the formula. Also check that calculation is enabled if other formulas are not updating.
For a quick audit, filter or sort the source column and inspect a few cells counted by the formula and a few that are not. That helps identify whether the issue is the range, the underlying data type, or invisible characters, rather than a problem with COUNTIF itself.
Choosing the right method for your task
Start by translating “not blank” into a precise rule. It may mean any text, any value, text with a visible character, or text matching a category. These definitions produce different totals in a mixed or formula-driven sheet.
- Count text, including text-formatted IDs: COUNTIF with
"*". - Count any populated cell: COUNTA, while accounting for formula cells that may display empty strings.
- Count text results with at least one character: SUMPRODUCT with ISTEXT and LEN.
- Count text that follows a pattern: COUNTIF with wildcard characters such as * or ?.
For repeatable reporting, write the definition next to the result. A label such as “Text entries (excluding zero-length results)” is more informative than “Nonblank,” especially when someone else maintains the workbook or compares the total with a separate data source.
Frequently Asked Questions
Does COUNTIF with an asterisk count numbers?
It counts text that matches the wildcard, not ordinary numeric values. A number stored as text can be counted, so imported or leading-zero data may produce a different result than expected.
How do I count cells that are not blank, including numbers?
Use COUNTA for cells containing any kind of value. If formulas return empty strings, decide whether those formula cells should count and check the results against your intended definition.
Will a cell containing only a space count as text?
Yes. A space is a text character, even though the cell may look empty. Use a length check that trims spaces if ordinary leading and trailing spaces should not qualify.
Can COUNTIF distinguish uppercase from lowercase?
No. COUNTIF text criteria are case-insensitive. If capitalization is meaningful, use a case-sensitive formula approach rather than COUNTIF.
Why does my COUNTIF result not match the visible entries?
Check for numbers stored as numbers versus text, formulas returning empty strings, spaces, and an incorrect range. Those are common reasons that what looks blank or populated on screen differs from the value Excel evaluates.
Related
- Inventory Checklist Excel Template: What to Include
- Inspection Log Checklist in Excel: What to Include
- Checklist in Excel: Template Setup and Practical Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Smartsheet SUMIF Examples for Everyday ReportsIn Smartsheet, SUMIF adds values in a sum range only when corresponding cells meet one condition. For example,…
- Sumifs with date range?Learn how SUMIFS handles dates, boundaries, months, extra criteria, and common errors, with clear Excel examples to help…
- Excel Filter Does Not Contain Text: How to Exclude MatchesTo show rows whose cells do not include particular text, open the column’s filter menu and choose Text…
- Excel Formula to List All Sheet Names in a WorkbookExcel has no built-in worksheet formula that directly returns every sheet name. In Excel for Microsoft 365 on…
- How to Filter Duplicates in ExcelTo filter duplicate rows in Excel for Microsoft 365 on Windows, add a helper column with a formula…
- Excel Formula for List of Unique Values: 10 Ways That WorkFor a basic list of distinct entries in Microsoft 365, with source values in A2:A20, enter =UNIQUE(A2:A20) in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.