To count cells containing text in Excel, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. The asterisk is a wildcard that matches text of any length; ordinary numbers and genuinely blank cells are not counted.
The basic COUNTIF formula
For example, suppose A2:A8 contains North, the number 125, Pending, a blank cell, a date, the text value 0, and South. With those entries in A2:A8, the formula =COUNTIF(A2:A8,"*") returns 4: North, Pending, text-formatted 0, and South. The numeric value and date are not text, and the blank is not counted.
COUNTIF takes a range and a condition. Here, the range is A2:A8 and the condition is the wildcard criterion “*”. Excel for Microsoft 365 on Windows uses commas between function arguments in the formulas shown here. In some regional settings, Excel expects semicolons instead; if a formula with commas is rejected, your list-separator setting may be the reason.
The formula works the same way in Excel for Mac and Excel for the web. You can enter it in a cell outside the range, such as B2, and press Enter to see the count. Avoid putting the result formula inside the range it counts, or you may create a circular reference.
What the asterisk wildcard matches
In COUNTIF criteria, an asterisk represents zero or more characters. That makes “*” a convenient way to ask Excel to find text entries without specifying their contents. It can match a single letter, a longer phrase, or text containing numbers, such as an ID stored as text. It is not a test for whether a cell merely looks populated.
Data type matters. A number formatted to look like text is still a numeric value if Excel stores it as a number, so the wildcard criterion does not count it as text. Conversely, a value such as 00123 imported or entered as text is a text string and is counted. Dates are usually stored as serial numbers with date formatting, so they are not counted as text unless they have actually been stored as text.
COUNTIF matching is not case-sensitive. For this task, that usually does not matter: the formula counts both uppercase and lowercase text. It also counts text that consists only of spaces, because spaces are characters. That detail can make a count higher than the number of cells that appear to contain meaningful words.
Count only text with at least one character
A useful variation is to require at least one character rather than allowing a zero-character match. With text entries in A2:A100, use =COUNTIF(A2:A100,"?*"). The question mark matches one character and the asterisk matches any additional characters, so together they match text strings that are at least one character long.
This is especially useful when cells contain formulas that display an empty string. A cell containing a formula such as one that returns “” is not physically empty, even though it looks blank. The criterion “?*” excludes a zero-length result, while a result containing a space still has a character and can be counted. If spaces should not count as meaningful content, remove or clean them in the source data before counting.
There is another distinction worth knowing: a formula can count cells whose values Excel identifies as text, including zero-length text results. With any values in A2:A100, =SUMPRODUCT(--ISTEXT(A2:A100)) returns the number of cells whose evaluated values are text. This differs from a count of visible, nonempty entries, so choose it when the question is about data type rather than whether a text value has visible characters.
Count cells containing a particular word or phrase
To count text cells that include a word or fragment, put a wildcard on each side of the search term. With descriptions in A2:A100, =COUNTIF(A2:A100,"*east*") counts cells containing east anywhere in the text, such as East warehouse or Northeast. The result is case-insensitive, so EAST and east both match.
This is a partial-match count, not a whole-word search. Searching for art, for instance, can match cart and artist as well as a cell containing the standalone word art. If the distinction matters, use a more specific phrase or clean and standardize the source values. COUNTIF supports the wildcard question mark for one character as well as the asterisk for any number of characters.
If the search term itself includes a wildcard character and you need to match it literally, Excel uses a tilde to escape wildcard characters in criteria. A tilde can also be escaped when you need to search for a literal tilde. Take care when building criteria from user-entered search text, because a star or question mark may otherwise be interpreted as a wildcard rather than as an ordinary character.
Choose a formula for the question you mean
“Count cells with text” can mean different things in a worksheet. Pick the formula based on whether you care about text type, visible characters, or a particular match:
- Any text value: use
=COUNTIF(A2:A100,"*")for a straightforward count of text entries. - Text with at least one character: use
=COUNTIF(A2:A100,"?*")to exclude zero-length results. - Cells whose evaluated value is text: use
=SUMPRODUCT(--ISTEXT(A2:A100)), including when the distinction between text and numbers is the point. - Text containing a phrase: use
=COUNTIF(A2:A100,"*east*"), changing east to the fragment you want to find.
Before using any result in a report, decide how you want to treat whitespace, formula-generated blanks, text-formatted numbers, and error values. A count can be mathematically correct while answering a different question from the one a reader assumes. For example, a column containing spaces entered by mistake may have more text cells than meaningful responses.
COUNTIF compared with COUNTA and COUNT
COUNTIF is not interchangeable with the other common counting functions. COUNTA counts cells that are not empty, including text, numbers, and formulas that return an empty string. COUNT counts numeric values, including dates stored as numbers, but does not count ordinary text. COUNTIF with “*” focuses on text matches; COUNTIF with “?*” focuses on text containing at least one character.
With names in A2:A20 and amounts in B2:B20, for instance, use COUNTA on the names when you want every nonempty entry, regardless of its type. Use COUNT on the amounts when you want numeric entries. Use COUNTIF with a wildcard when the task specifically asks how many cells in a range contain text. The formulas may return the same result in a clean column, but they can diverge as soon as numbers, formula results, or blanks are mixed in.
Common problems and ways to check the result
If the count seems wrong, inspect the underlying values rather than relying only on what the cells display. A value can look numeric but be stored as text, or look blank while containing a formula or spaces. A filter also does not automatically change COUNTIF into a visible-rows-only count: COUNTIF evaluates the referenced range, including rows hidden by filtering.
For a quick check, compare the result with a small sample of cells and consider these common causes:
- Unexpectedly high count: cells may contain spaces, text-formatted numbers, or formula results that are text.
- Unexpectedly low count: entries that look like text may actually be numbers or dates stored numerically.
- Blank-looking cells: check whether they contain formulas or invisible characters; use “?*” when a text value must contain at least one character.
- Only visible rows needed: COUNTIF includes hidden and filtered-out rows. A filtered-list count needs a different approach designed to account for visible rows.
- Formula rejected: check the range reference, quotation marks, parentheses, and whether your regional settings use semicolons as separators.
When a data import is the source of the problem, standardizing the column can be more reliable than making the counting formula increasingly complicated. Decide whether IDs and numbers should be stored as text or numeric values, remove accidental spaces where appropriate, and keep a consistent format for entries. Then use the count that matches the cleaned data.
Frequently Asked Questions
Does COUNTIF with “*” count numbers?
No, not when Excel stores them as numbers. A number that has been stored as text can match the wildcard and be counted, even if it looks like a number on the worksheet.
How do I count cells that are not blank?
For ordinary nonempty cells of any type, use COUNTA. Be aware that COUNTA includes formulas that return an empty string. If you mean text with at least one character, COUNTIF with the criterion “?*” is more targeted.
Does the formula count text in hidden or filtered rows?
Yes. COUNTIF evaluates the full range you reference, including rows hidden manually or excluded by a filter. It is not a visible-cells-only count.
Can I count text that contains a specific word?
Yes. Use a criterion with asterisks before and after the fragment, such as “*east*”. COUNTIF treats the match as case-insensitive and can match the fragment inside a longer word.
Why is a blank-looking cell included?
It may contain spaces or a formula that returns text, even if nothing visible appears in the cell. Try the “?*” criterion to exclude zero-character text results, and inspect the cell contents if spaces may be involved.
Does this formula work in Excel for Mac or the web?
Yes. COUNTIF and the wildcard criteria described here work in Microsoft Excel for Mac and Excel for the web as well as Excel for Microsoft 365 on Windows. Argument separators can vary with regional settings.
Related
- Data Validation Named Range in Excel: Setup and Fixes
- Data Validation Reference Table in Excel
- Remove Duplicates in Power Query: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula Solver: Goal Seek, Solver, and How to Use ThemExcel does not have a worksheet function called “formula solver.” To make Excel find an input that produces…
- Can You Find and Replace Formulas in Excel?Yes. In Excel for Microsoft 365 on Windows, press Ctrl+H, choose Options if needed, set Look in to…
- Excel INDIRECT: Link to Another Tab with a FormulaTo refer to a cell on another worksheet using a sheet name stored in A1, enter =INDIRECT("'"&A1&"'!B2"). If…
- Paste Values in Excel: Keep Results, Remove FormulasTo get paste values in Excel, copy the cells, select where the results should go, then choose Home…
- VLOOKUP Examples for Beginners: A Practical Excel GuideIn Excel, VLOOKUP finds a value in the first column of a range and returns related information from…
- AVERAGEIF Across Sheets in Excel: Formulas & ExamplesExcel’s AVERAGEIF function cannot directly average matching values across multiple worksheets in one formula. Assuming Jan and Feb…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.