Google Sheets: Count Cells Containing Text?

Yes. In Google Sheets, use =COUNTIF(A2:A100,"*") to count cells containing text in the range A2:A100. If you want to require at least one character and exclude empty-string results, use =COUNTIF(A2:A100,"?*").

What the basic formula counts

COUNTIF checks a range against one condition. In its condition, an asterisk is a wildcard for a sequence of characters, so "*" matches text of varying lengths. Replace A2:A100 with the range you want to inspect. For example, with names in B2:B50, use =COUNTIF(B2:B50,"*").

The formula is useful when a column mixes names or notes with numbers, dates, and empty cells. It counts text entries rather than every occupied cell. A number such as 42 and a date stored as a date value are not text, even if Sheets displays them with punctuation or a date format. A value that looks numeric but was imported or entered as text, such as the text string 0042, is text and can be counted.

For a quick example, suppose A2:A7 contains the text entries “North,” “South,” and “Pending,” the number 18, a date, and a genuinely empty cell. The wildcard formula returns 3. It does not count the number, the date value, or the empty cell.

Use ?* when empty text results matter

An asterisk can represent zero or more characters. If your range contains formulas that display an empty string, or you want to insist that a text cell has at least one character, "?*" is a more specific condition: the question mark requires one character, and the asterisk allows additional characters. Thus =COUNTIF(A2:A100,"?*") counts text entries with at least one character.

This distinction is helpful in sheets where formulas return an empty string using "" to make a cell look blank. Such a cell is not the same as a cell that has never contained anything; it contains a formula. If your count needs to reflect visible, nonempty text, use the question-mark version and check that it matches the intended rows.

A cell containing one or more spaces still has characters. Both wildcard formulas can count it, although it may look blank on the sheet. If spaces should not count, clean the values or use a formula that tests the trimmed text. For a range A2:A100, a suitable option is =SUMPRODUCT(--ISTEXT(A2:A100),--(LEN(TRIM(A2:A100))>0)). This counts text only when removing leading and trailing spaces leaves at least one character. It also means a text value made entirely of spaces is excluded.

Count text with a specific word or phrase

To count cells containing a particular word or phrase anywhere in the text, put an asterisk on both sides of the search term. For instance, if A2:A100 contains comments, =COUNTIF(A2:A100,"*late*") counts cells whose text contains “late,” including “late delivery” and “arrived late.” This is a substring search, not a whole-cell match.

You can keep the search term in a cell so it is easy to change. If D1 contains late, use =COUNTIF(A2:A100,"*"&D1&"*"). The ampersands join the wildcard characters to the contents of D1. If D1 is empty, the resulting condition is effectively a wildcard for any text, so a blank search term may return a much larger count than expected.

In Google Sheets, COUNTIF matching is not case-sensitive: a search for “late” can match “Late” or “LATE.” The wildcard characters also have special meanings. An asterisk matches a sequence, and a question mark matches one character. To search for a literal asterisk or question mark in the cell text, prefix that character with a tilde in the criterion. For example, the criterion "~*" looks for a literal asterisk rather than using it as a wildcard.

Count text in cells that meet another condition

Sometimes the question is not simply how many cells contain text, but how many text entries belong to a particular group. COUNTIFS can apply multiple conditions. Suppose department names are in A2:A100 and notes are in B2:B100. To count rows for Sales where the note cell contains text, use =COUNTIFS(A2:A100,"Sales",B2:B100,"*").

For a partial text match within the second range, the condition can include wildcards: =COUNTIFS(A2:A100,"Sales",B2:B100,"*urgent*"). This counts rows where the department is Sales and the note contains “urgent.” Make sure each range supplied to COUNTIFS has the same dimensions; mismatched range sizes can cause an error or an unexpected result.

Criteria are evaluated by row in these examples. The result is the number of rows satisfying both conditions, not the number of matching words inside a cell. If a single note says “urgent, urgent, urgent,” it still contributes one matching row.

Choose the right counting method

Different counting functions answer different questions. Choosing the one that matches your definition of “text” prevents a plausible-looking but misleading total.

COUNTIF(range,"*") is a concise way to count text entries with a wildcard condition.
COUNTIF(range,"?*") requires at least one character and is useful when formulas may return empty strings.
COUNTA(range) counts nonempty values of many kinds, including text, numbers, and logical values. It is not a text-only count.
COUNT(range) counts numeric values, including dates stored as numbers, rather than ordinary text entries.
ISTEXT(value) tests whether one value is text. For an ordinary range-wide text count, COUNTIF is usually simpler; for a more tailored rule, combine tests in an array formula.

For example, if a column contains 10 text entries, 4 numbers, and 2 dates, COUNTA can return 16 because all are occupied values, while the wildcard text count returns 10. A date that was imported as a text string may be counted as text instead. The underlying value and its display format are different things: changing a number’s format to look like a date does not turn the number into text.

Common reasons the result looks wrong

Start by checking the range. A formula referring to A2:A100 will not include text in A101, and a range that includes a heading may count the heading too. If the count is meant to cover data rows only, exclude the header deliberately. Also check whether there are hidden rows or filters: filtering changes what you see, but COUNTIF still evaluates the cells in its stated range, including rows hidden by a filter.

Numbers stored as text: A value such as “123” can be counted as text if it is stored as text, even though it looks numeric. This can happen with imported data or leading apostrophes.
Spaces: A cell containing a space is not truly empty. It may satisfy a wildcard count while appearing blank. TRIM can help identify or remove extra spaces.
Formulas returning empty strings: A cell that displays nothing may still contain a formula. Use "?*" when at least one character is required, and distinguish that rule from counting cells that merely contain formulas.
Errors: An error value is not a text entry just because it displays a message. If you need to count error cells, that is a separate task and should use an error-specific test.
Wrong separator: Google Sheets formulas commonly use commas between arguments in English-language settings. Spreadsheet locale settings can affect separators and number formats, so follow the syntax accepted by your file.

When diagnosing a surprising total, try the formula on a small, known range first. You can also click a questionable cell and inspect the formula bar: that helps distinguish a truly empty cell, a text value consisting of spaces, a number formatted as text, and a formula whose displayed result is blank.

Using the formula in Google Sheets

Click the cell where you want the result, type the formula, and press Enter. To count text in a whole column, you can use a reference such as =COUNTIF(A:A,"*"). For a large sheet, a bounded range like A2:A1000 can be easier to audit and may avoid calculating over many unused rows. Make sure the result cell is outside the range being counted; otherwise, you can create a circular reference if the formula includes itself.

Copying the formula to another location changes relative references as appropriate. To keep the range fixed when copying, add dollar signs: with data in A2:A100, =COUNTIF($A$2:$A$100,"*") keeps that range anchored. In a browser, Google Sheets saves changes automatically, but confirm that the formula result has updated before relying on it.

Google Sheets and Excel share the COUNTIF wildcard approach for this basic task, but menu steps and some advanced functions can differ between the programs. These formulas are for Google Sheets in a web browser. If you move a file to Excel, verify any more elaborate formula against the Excel version and settings you use.

Frequently Asked Questions

Does COUNTIF with an asterisk count numbers?
No, not when the cell contains a numeric value. It can count a value that looks like a number if that value is stored as text. A date stored as a date value is also numeric for this purpose, even if it is displayed in a date format.

How do I count cells that contain a word anywhere?
Use a wildcard on each side of the word, such as =COUNTIF(A2:A100,"*apple*"). That counts cells containing the sequence “apple” within longer text too, and the match is not case-sensitive.

How can I count text but ignore cells that only contain spaces?
For A2:A100, use =SUMPRODUCT(--ISTEXT(A2:A100),--(LEN(TRIM(A2:A100))>0)). TRIM removes leading and trailing spaces for the test, so a text cell made only of spaces does not qualify.

Does COUNTA count only text?
No. COUNTA counts occupied cells containing values such as text, numbers, and logical values. Use a text-specific wildcard condition when you want text rather than all nonempty values.

Why is a visually blank cell included in my count?
It may contain spaces or a formula that returns an empty string. Inspect the cell or formula bar. If your goal is to count entries with at least one character, try =COUNTIF(A2:A100,"?*"); if whitespace should not qualify, use a TRIM-based test.

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.