Count Cells With Text in Google Sheets: Formulas and Tips

To count cells containing text in Google Sheets, use =COUNTIF(A2:A100,"*"), replacing the range with your data. For a more precise count that also recognizes text returned by formulas, use =SUMPRODUCT(--ISTEXT(A2:A100)); the right choice depends on whether you mean any text value or only nonempty text.

What “cells with text” means

A cell can look like it contains text without being stored as text. Google Sheets distinguishes text values from numbers, dates, checkboxes, and genuinely empty cells. That distinction matters when counting: a date displayed as “Jan 15” is normally a numeric date value, and a number displayed with a currency format is still a number. A number typed with a leading apostrophe, such as '7, is stored as text instead.

There is also a difference between a cell that is truly empty and one containing a formula that displays nothing. A formula returning an empty string may look blank, but it is still a formula result. Depending on what you want your count to mean, you may want to include or exclude that cell. The formulas below cover both interpretations.

For a simple example, suppose A2:A7 contains the text North, the number 42, an empty cell, the text pending, a real date, and the text value 7. There are three text entries: North, pending, and 7. The numeric value, blank, and date are not text, even if formatting makes them look similar.

Count text with COUNTIF

COUNTIF is the quickest option for a straightforward count. Its asterisk wildcard represents any sequence of characters, so the criterion "*" matches text strings. With North, 42, an empty cell, pending, a date, and text 7 in A2:A7, this returns 3:

With the example values described above in A2:A7, enter =COUNTIF(A2:A7,"*"). It returns 3, counting North, pending, and the text-stored 7.

Change A2:A7 to the range you actually want to inspect. If row 1 contains a heading, start at row 2 so the heading is not included. A bounded range such as A2:A500 is often a better choice than an entire column: it documents the intended data area and avoids including unrelated cells farther down the sheet.

The asterisk means “zero or more characters,” so it is useful for finding text without needing to know what the text says. A cell containing a phrase is still one cell and contributes one to the count, not one for every word or character. COUNTIF is not case-sensitive for ordinary text matching, which is usually appropriate when counting entries rather than distinguishing capitalization.

Use ISTEXT for a type-based count

If the question is specifically “How many cells contain a value whose type is text?”, use ISTEXT with SUMPRODUCT. ISTEXT checks each cell’s value type, and SUMPRODUCT adds the resulting TRUE/FALSE tests after the double unary operator converts them to 1s and 0s.

With North, 42, a blank, pending, a date, and text 7 in A2:A7, enter =SUMPRODUCT(--ISTEXT(A2:A7)). It returns 3, because three cells contain text values.

This method is especially helpful when formulas in the range can return an empty string. For example, if A8 contains a formula that returns "", ISTEXT treats that result as text, even though the cell appears blank. With the same A2:A7 data and that empty-string formula in A8, =SUMPRODUCT(--ISTEXT(A2:A8)) returns 4. This is not a mistake: the formula is counting text-type results, including the empty string.

Choose ISTEXT when you care about the underlying value type or need to account deliberately for formula results. Choose COUNTIF when you want a compact, familiar formula for ordinary text entries and your range does not rely on formulas that display blank. If those formulas are present, compare the result with ISTEXT or use a nonempty-text test.

Count only nonempty text

Sometimes “cells with text” means cells containing at least one character, not cells that return an empty string. In that case, the wildcard criterion "?*" requires at least one character: the question mark matches one character and the asterisk can match any additional characters.

With North, 42, a blank, pending, a date, and text 7 in A2:A7, enter =COUNTIF(A2:A7,"?*"). It returns 3. If A8 contains a formula returning an empty string, expanding the range to A2:A8 still returns 3, because the formula result has no characters to match.

A space is a character. Therefore a cell containing one or more spaces can satisfy "?*", even though it may look empty on the sheet. If whitespace-only entries should not count, trim the text before testing its length.

With the previous A2:A8 values and a single space in A9, enter =SUMPRODUCT(--ISTEXT(A2:A9),--(LEN(TRIM(A2:A9))>0)). It returns 3: North, pending, and text 7 have non-space text, while the empty-string result and space-only cell are excluded. TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces. It does not remove every possible invisible character, such as a nonbreaking space copied from some web pages.

Choose the right range and counting rule

Before putting a formula into a report, decide what should count as a text cell. These are the most common interpretations:

Any text-type result: use ISTEXT with SUMPRODUCT. This includes text returned by formulas, even an empty string.
Text matching at least one character: use COUNTIF with "?*". A space-only value still qualifies.
Non-space text only: use the ISTEXT, LEN, and TRIM test. Consider whether copied nonbreaking spaces also need cleaning.
Text in a specific range: use the same method but limit the range to the relevant rows and column.

For a growing list, you might use a range such as A2:A5000 and extend it as needed. An entire-column reference can be convenient, but a very large range may make a busy sheet slower, particularly when many formulas repeatedly process it. Also check whether your range includes notes, labels, or helper cells that are not part of the records you intend to count.

Google Sheets formulas recalculate as values change. If you add a new text entry inside the range, the count updates automatically. If you add data below the last row of a bounded range, it will not be included until you expand that range. A table-style workflow can reduce that oversight: reserve a sufficiently large input area, or deliberately update the formula when adding rows.

COUNTIF, COUNTA, and COUNT are not interchangeable

A common source of confusion is using a general counting function for a text-only question. COUNTA counts cells that contain a value, including text, numbers, dates, and many formula results. It is useful for asking how many entries are present, but it does not isolate text. A formula returning an empty string may also affect whether a cell is treated as populated by a particular counting approach, so test the behavior against your sheet if formulas display blanks.

COUNT counts numeric values, including dates stored as numbers, rather than text. A value such as the text-stored 7 is not counted by COUNT, while a real numeric 7 is. The COUNTIF wildcard method selects text matching a criterion; ISTEXT checks the value type directly. For a text-only tally, do not substitute COUNTA or COUNT without confirming that their definitions match your goal.

Formatting does not change a value’s type. Applying a date format to a number does not turn it into text; changing a numeric value to plain-text storage does. If a result seems surprising, inspect how the value was entered or imported. Leading apostrophes, import settings, and formulas can all produce text that resembles a number or date.

Common problems and practical checks

The count is too high: look for cells containing spaces, formula results that are empty strings, or text outside the visible portion of the data. Try the nonempty or TRIM-based method if those should be excluded.
The count is too low: confirm that the range includes every data row and that entries you expected to count are actually stored as text. A date or numeric value is not text just because it is displayed with symbols.
The formula shows a parse error: check the range punctuation, quotation marks, and separators. Google Sheets may use semicolons instead of commas in some spreadsheet locales; the examples here use commas.
The count misses newly added rows: extend a bounded range or choose an appropriately larger range. Do not assume a formula ending at row 100 automatically includes row 101.
Invisible spaces remain: TRIM handles ordinary spaces, but pasted data can contain other whitespace characters. Clean the source or identify the specific character before choosing a more complex cleanup formula.

To investigate a suspicious cell, select it and look at the formula bar and formatting. You can also test one value in a spare cell with ISTEXT or ISNUMBER, then remove the temporary check when finished. This helps separate a counting-formula issue from a data-entry issue.

Using the formula in Google Sheets

In Google Sheets in a web browser, select the cell where you want the total, type or paste the chosen formula, and press Enter. The cell displays the result while the formula bar shows the calculation. You can also begin typing a function name in a cell to see Google Sheets’ function suggestions, but confirm that the selected range and criteria match your intended definition.

These formulas count cells in the specified range whether or not rows are hidden or excluded by a filter; they are not a “visible rows only” count. If a report must count only currently visible records, define that as a separate requirement and choose an approach designed for filtered data rather than assuming COUNTIF changes with the filter.

Excel has COUNTIF, ISTEXT, and SUMPRODUCT too, and the examples are broadly familiar to Excel users. This page describes Google Sheets in a browser; menu names and interface details differ between products, and spreadsheet locale settings can affect argument separators. In either program, validate the formula on a small sample where you already know which cells should be included.

Frequently Asked Questions

How do I count cells that contain any text in Google Sheets?

Use =COUNTIF(A2:A100,"*") and replace A2:A100 with your range. It counts matching text strings, not numeric values or dates. If formulas return empty strings in the range, use ISTEXT with SUMPRODUCT when you want to count text-type results precisely.

Does COUNTIF with an asterisk count numbers?

No, not when the cell contains a genuine numeric value. The "*" criterion is a text wildcard. A number stored as text, such as a value entered with a leading apostrophe, can be counted because its underlying type is text.

How can I exclude blank-looking formula results?

Use =COUNTIF(A2:A100,"?*") to require at least one character. This excludes an empty string but still counts a cell containing spaces. If spaces should not count, use a TRIM-and-LEN test with ISTEXT, as shown above.

Why does a date not count as text?

Google Sheets stores real dates as numeric values and displays them using a date format. A date imported or entered as a text string may count as text instead. The visible appearance alone does not establish the underlying value type.

Can I count text across an entire column?

Yes. You can replace a bounded range with a column reference, such as A:A. A bounded range is often easier to audit and can avoid unnecessary calculation across unused rows, so use a whole column only when its convenience is worth that trade-off.

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.