Google Sheets: Count Cells That Contain Text Values

To count cells containing text in Google Sheets, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. It counts text entries, including text that looks like a number, but not ordinary numeric values; if you need to exclude formula results that look blank, use a stricter formula described below.

The basic COUNTIF formula

COUNTIF checks a range against a condition. The asterisk in the condition is a wildcard meaning “any sequence of characters,” so the criterion "*" matches cells containing text. The range in the example starts at row 2 to leave out a likely header and ends at row 100; adjust both endpoints to fit your sheet.

For example, if A2:A8 contains North, South, 42 as a numeric value, in progress, a blank, pending, and 0 as a numeric value, the formula returns 3. The three text entries are counted, while the numeric values and truly blank cell are not. A value such as 42 entered or imported as text would count, even though it looks like a number.

Use a bounded range when you know how much data the sheet will contain. It keeps the formula focused and makes the intended data area clear. If new records are regularly added below the current data, you can use an open-ended column range instead, but remember that it includes the header unless you start below it.

Count text in a whole column or a changing range

For a list in column A with a header in row 1, use =COUNTIF(A2:A,"*"). This looks from A2 to the bottom of the column, so added rows are included automatically. If the column has no header, use =COUNTIF(A:A,"*"). In either case, put the formula outside the range it counts; placing a whole-column count formula in column A can create a circular reference.

A whole-column range is convenient, but it can be less efficient or harder to audit in a large workbook with many formulas. If your data has a known limit, a range such as A2:A5000 makes the scope explicit. Another practical option is to count a generous but bounded area and extend it when the dataset grows.

In Google Sheets, enter the formula in a blank cell and press Enter. The result updates when values in the counted range change. Formula argument separators can depend on the spreadsheet’s locale: if the comma-separated version is rejected, check whether your locale expects semicolons between arguments.

Count text while excluding formula-generated blanks

There is a useful distinction between an actually empty cell and a cell containing a formula that displays an empty string, often written as "". The cell may look blank on the grid but still contain a formula. Wildcard criteria and blank-like values can be confusing in mixed data, so use an explicit type-and-length test when the requirement is specifically “text with at least one character.”

With the values to check in A2:A100, use =SUMPRODUCT(--ISTEXT(A2:A100),--(LEN(A2:A100)>0)). This counts cells that Google Sheets identifies as text and whose text length is greater than zero. It excludes numeric values, truly empty cells, and formulas that return an empty string. If the cells contain spaces, however, a space has a length greater than zero and is counted. To treat spaces-only entries as empty too, use =SUMPRODUCT(--ISTEXT(A2:A100),--(LEN(TRIM(A2:A100))>0)).

These stricter formulas are useful for imported reports or columns filled by formulas. They are more explicit than a wildcard count, but they also require you to decide what “text” means for your task. For a straightforward list of ordinary text values, COUNTIF is shorter and easier to maintain.

Count cells containing a particular word or phrase

You can adapt the wildcard criterion to find text that contains a specified phrase. With the values in A2:A100, use =COUNTIF(A2:A100,"*apple*") to count cells containing “apple” anywhere in the text. For example, it matches “green apple” and “applesauce” because the asterisks allow other characters before or after the word.

To count entries that start with a particular word or code, put the wildcard at the end: =COUNTIF(A2:A100,"North*"). To count entries ending in a phrase, put it at the beginning: =COUNTIF(A2:A100,"*Street"). COUNTIF text matching is generally not case-sensitive, so “Apple” and “apple” match the same criterion.

Wildcards have a special meaning. If you need to search for a literal asterisk or question mark rather than use it as a wildcard, prefix that character with a tilde in the criterion. For example, a criterion containing ~* treats the asterisk as a character to match. This matters when counting product codes or notes that include wildcard symbols.

How text differs from numbers, dates, and blanks

Google Sheets stores values by type, not just by the way they appear. A cell displaying 00123 might contain text, while a cell displaying 123 may contain a number. The first can be counted by a text wildcard; the second is not. Formatting a numeric value to look like text does not necessarily change its underlying type.

Dates are usually stored as numeric values with date formatting. Therefore, a date displayed as “Nov 3, 2026” is normally treated as a number for type-checking purposes, not as a text string. If dates were imported as literal strings, they can be counted as text. The same issue arises with percentages, currency, and identifiers: appearance alone does not tell you whether a cell contains text or a numeric value.

Whitespace is another common source of unexpected results. A cell containing one or more spaces is not truly empty. It contains text characters and can be included in a text count. If your source data has accidental leading or trailing spaces, consider cleaning it in a helper column with TRIM, or use the stricter formula above to ignore entries made only of spaces. Do not remove spaces blindly if they are meaningful in the data.

COUNTIF compared with COUNTA and ISTEXT

Choose the function according to what you mean by “has text.” COUNTIF(range,"*") is a convenient count of entries matching a text wildcard. COUNTA counts nonempty values of many kinds, not just text, so it can include numbers, dates, errors, and formulas. ISTEXT checks the type of a value and is helpful when a strict type-based count is needed.

For instance, with a mixed list containing names, numbers, dates, and error values, COUNTA answers “how many cells are populated?” It does not answer “how many contain text?” A text wildcard or an ISTEXT-based formula is more appropriate for the second question. Conversely, if the goal is to count every filled-in record regardless of type, COUNTA is usually the more direct choice.

Also separate “contains text” from “is nonblank.” A cell with the number zero is nonblank but is not text. A cell with a formula returning an empty string may look blank but still contain a formula. Decide whether your count is based on visible content, underlying value type, or whether a formula exists, then select the formula that matches that definition.

Common problems and ways to check the result

The result is lower than expected: Some entries that look like words may actually be numbers or dates, or the formula range may stop before the last row. Check the referenced range and inspect the source values.
The result is higher than expected: The range may include a header, notes, or spaces-only cells. Start below the header and decide whether whitespace should count.
Numbers are being missed: That is expected if they are numeric values. If identifiers such as postal codes need to count as text, confirm that they are stored as text rather than merely formatted to display a certain way.
The formula produces a parse error: Check that quotation marks are straight double quotes and that the argument separator matches the spreadsheet locale. Make sure the range and parentheses are complete.
The count changes unexpectedly: Check for formulas that return blank-looking results, new entries in an open-ended range, and changes to the source data. A helper column can make imported or inconsistent data easier to inspect.

For a quick audit, try a small sample range where you can see every value and compare the count with the entries you consider text. You can also temporarily test an individual cell with =ISTEXT(A2), assuming the value you want to inspect is in A2. It returns TRUE for a text value and FALSE for a number or a truly empty cell. This check helps distinguish a formatting issue from a counting-formula issue.

Frequently Asked Questions

Does COUNTIF with an asterisk count numbers?
No, not when the cells contain actual numeric values. It can count digits that are stored as text, because Sheets treats those entries as text even though they look numeric.

How do I count cells that contain any value, not just text?
Use COUNTA on the range, such as =COUNTA(A2:A100) when the data is in A2:A100. It counts populated cells of different types, so it is not a text-only count.

Does COUNTIF count cells with formulas that display nothing?
Those cells are not truly empty: they contain formulas. Their handling can be surprising in wildcard-based counts. If you want only nonempty text characters, use the ISTEXT and LEN formula above, which excludes zero-length results.

Can I count text in more than one column?
Yes. If the cells are in a single rectangular block, use that block as the range, for example =COUNTIF(A2:C100,"*"). It counts matching text cells across all three columns, not rows that contain text as a group.

Is the formula the same in Excel?
The basic COUNTIF wildcard approach is also available in Excel, but this page describes Google Sheets in a browser. If you move a workbook between spreadsheet programs, check the results for formula-generated empty strings and locale-specific separators, since those details can affect how a count behaves.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.