Google Sheets Formula to Count Cells With Text Values

To count cells containing text in Google Sheets, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. It counts text entries while excluding ordinary numbers and truly empty cells, making it a quick way to measure how many labels, names, or notes a column contains.

The quickest formula for counting text

COUNTIF checks a range against a condition. In this formula, the asterisk is a wildcard that matches text of any length, including a single character or a longer phrase. The formula returns one total for the whole range rather than marking each matching cell.

For example, with names or other entries in A2:A100, enter the formula in an unused cell, such as B1. Google Sheets updates the result when values in the checked range change. You can also select a range on another sheet by including its sheet name, such as Sheet2!A2:A100.

The asterisk is not a literal character here: it means “any sequence of characters.” This is why the formula can count varied entries such as “North,” “In progress,” and “x” without knowing their exact contents in advance. It does not mean “count every cell that looks occupied,” however; numbers and cells containing formulas need a little more attention.

What the formula counts—and what it does not

The wildcard criterion is designed to match text, not numeric values. A cell containing the number 42 is not counted by the text-count formula, even if the cell is formatted to display as text or has a currency or date format. A value such as 42 that was imported or entered as the text string “42” is text and can be counted. The underlying value type, not merely its appearance, matters.

True blank cells are excluded. A cell with a space in it is different: it contains a character, so the wildcard can match it. Likewise, a cell containing a formula that displays an empty string is not necessarily the same as a never-used blank cell. When it is important to count only text with at least one character, use a criterion that requires a character.

With values in A2:A100, this version counts text entries that contain at least one character and excludes an empty string returned by a formula:

=COUNTIF(A2:A100,"?*")

The question mark matches one character, and the asterisk matches any characters after it. A space still satisfies the one-character requirement, so this formula does not automatically ignore cells containing only spaces.

A worked example with mixed values

Suppose A2:A8 contains the following: North, the number 12, pending, a truly blank cell, the text value “0,” the number 0, and Closed. The text-count formula returns 4: it counts North, pending, text “0,” and Closed. It does not count either numeric value or the blank cell. The text “0” and numeric 0 may look alike in the grid, but they are different kinds of values.

With that exact example in A2:A8, this formula counts every nonempty cell, including text and numbers:

=COUNTA(A2:A8)

COUNTA returns 6 for the example. It is useful when the question is “How many cells are occupied?” rather than “How many cells contain text?” Its result may also include a formula that displays an empty string, so it is not a direct substitute for a text-only count.

If the result is unexpectedly high or low, check the source values themselves. A number stored as text may be included in a text count, while a visually blank cell containing a space may also be included. Selecting a cell and looking at its formula bar can help distinguish what is actually stored from how the value is formatted.

Count text that meets a specific condition

COUNTIF can also count text matching a particular word or phrase. With status values in B2:B50, this formula counts cells equal to “Complete”:

=COUNTIF(B2:B50,"Complete")

COUNTIF matching is not case-sensitive, so this counts “Complete,” “complete,” and “COMPLETE” as matches. It requires the cell value to match the criterion rather than merely contain the word as part of a longer phrase.

To count cells that contain a word anywhere in the text, place wildcards before and after the word. With descriptions in C2:C50, the following formula counts cells containing “urgent,” including phrases such as “Urgent: call supplier”:

=COUNTIF(C2:C50,"*urgent*")

Wildcard criteria are useful for quick checks, but they are not a substitute for cleaning inconsistent data. For instance, a spelling variation or extra punctuation can make a cell fail a condition that expects a different phrase. If a criterion includes wildcard characters that should be treated literally, take care to distinguish the literal characters from their wildcard meaning.

Ignore cells that contain only spaces

COUNTIF with “?*” excludes empty strings but still counts a cell containing one or more spaces. That can happen when data is pasted from another source or when someone presses the space bar instead of clearing a cell. For a text count that also ignores values that become empty after trimming ordinary spaces, use SUMPRODUCT with ISTEXT, LEN, and TRIM.

With potentially messy text in A2:A100, this formula counts text cells whose trimmed contents have a length greater than zero:

=SUMPRODUCT(--ISTEXT(A2:A100),--(LEN(TRIM(A2:A100))>0))

TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words. ISTEXT keeps numeric cells out of the total, while the length test rejects text that is empty after trimming. In Google Sheets, this formula returns a single count; it does not alter the source cells. TRIM does not remove every possible invisible character, such as some nonbreaking spaces, so data copied from websites may need additional cleanup.

Choosing between COUNTIF, COUNTA, and COUNT

These functions answer different questions, so choose based on what “count” means in your sheet:

COUNTIF with a text wildcard: use it to count text entries, including names, labels, and descriptions, while excluding ordinary numeric values.
COUNTIF with a specific criterion: use it to count a particular status, category, or text fragment.
COUNTA: use it to count cells that are not empty, regardless of whether they contain text, numbers, or other values.
COUNT: use it to count numeric values. It is not a text-counting function.

For example, a survey column might contain written answers, a numeric rating, and blank responses. A text count estimates how many cells contain written responses; COUNTA counts responses of either type; COUNT gives the number of numeric ratings. None is universally “better”—the right choice depends on what you want the total to represent.

Common problems and ways to check the result

The count is too large: look for spaces, formula-generated empty strings, or numbers stored as text. Use “?*” to require at least one character, or the TRIM-based option if spaces should be ignored.
The count is too small: check whether the entries are actually numbers or dates, rather than text. A date formatted to look like a label is still usually a numeric date value and will not be included in a text-only count.
The range misses some rows: verify the start and end references. A formula using A2:A100 will not include data in A101 or a heading in A1.
The formula displays an error: confirm the range reference and quotation marks. In Google Sheets, the criterion must be inside straight double quotation marks.

To inspect a suspicious value, select its cell and check the formula bar, or try changing the cell format to a neutral format such as Automatic. Formatting can affect display, but it does not necessarily convert a number into text. If the spreadsheet uses a filter, remember that COUNTIF evaluates the cells in its referenced range whether or not rows are currently hidden by that filter; it is not automatically a visible-rows-only count.

Using the formula in Google Sheets

In Google Sheets in a web browser, click the cell where you want the answer, type the formula, and press Enter. You can type the range directly or select it with the mouse while entering the formula. To make a simple summary clearer, add a nearby label such as “Text entries” so another reader knows what the number represents.

Google Sheets recalculates the count when relevant values change. If you copy the formula to another location, check whether its range should move too. Ordinary references such as A2:A100 adjust when copied; if you need to keep the same range fixed while copying, use dollar signs in the reference, as in $A$2:$A$100.

The COUNTIF wildcard approach is also familiar to Excel users, but this article describes Google Sheets behavior and its browser interface. The formula itself is not a special Sheets-only trick. If you open the file in Excel, verify the result and any workbook-specific formula settings there, especially if the source data includes formulas returning empty strings or unusual imported characters.

Frequently Asked Questions

How do I count cells with text but not numbers in Google Sheets?
With the cells to check in A2:A100, use =COUNTIF(A2:A100,"*"). It counts text values and excludes ordinary numeric values. Use “?*” instead if you also need to exclude formula results that are empty strings.

Does COUNTIF with an asterisk count blank cells?
It does not count ordinary, truly empty cells as text. A cell containing a space is not truly empty, however, and a formula returning an empty string can differ from a never-used cell. The “?*” criterion requires at least one character.

How can I count cells containing a certain word?
With the search range in C2:C50, use =COUNTIF(C2:C50,"*urgent*") to count cells containing “urgent” anywhere in their text. COUNTIF is not case-sensitive.

How do I count text and numbers together?
With all relevant entries in A2:A100, use =COUNTA(A2:A100) to count nonempty cells. Be aware that COUNTA can include cells containing formulas that display an empty string.

Why is my text count including cells that look blank?
Those cells may contain spaces or formulas returning empty strings. To ignore ordinary spaces as well, use the TRIM-based SUMPRODUCT formula above; for unusual copied characters, additional cleanup may be needed.

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.