Google Sheets Function to Count Cells Containing Text

In Google Sheets, use =COUNTIF(A2:A20,"*") to count cells containing text in the range A2:A20. The asterisk is a wildcard that matches text of any length; if you want to count only text entries with at least one character, use =COUNTIF(A2:A20,"?*").

The basic COUNTIF formula

COUNTIF checks a range against one condition and returns how many cells meet it. For the first formula above, the assumed layout is any data in A2:A20; the result is the number of cells in that range whose contents are text. Replace A2:A20 with the cells you want to inspect, such as a column of names, notes, or product descriptions.

For example, suppose A2:A7 contains the entries Kiwi, 12 (a number), in stock, a blank cell, 3 apples, and Pending. With that layout, =COUNTIF(A2:A7,"*") returns 4: Kiwi, in stock, 3 apples, and Pending are text values. The number 12 and the blank cell are not counted. A value such as 3 apples counts because the cell’s value is text even though it includes a digit.

To enter the formula, click an empty cell, type it, and press Enter. In Google Sheets in a web browser, you can also select a cell and enter the formula in the formula bar. The count updates when the underlying values change. Excel has a similar COUNTIF function and wildcard behavior, but this page’s formulas and steps are for Google Sheets.

What the asterisk and question mark match

In a COUNTIF criterion, an asterisk (*) matches any number of characters, including none, while a question mark (?) matches one character. Putting them together as ?* requires at least one character: the question mark matches the first character and the asterisk matches any characters after it. That makes this criterion useful when you want to avoid counting empty text results.

With values in A2:A20, =COUNTIF(A2:A20,"*") is the general count of text cells. With the same layout, =COUNTIF(A2:A20,"?*") counts text entries that have at least one character. The second formula will not count a cell whose formula displays an empty string, such as a formula returning "", because that result has no characters to match. This distinction matters in sheets where formulas fill a column but show nothing until another cell is completed.

Wildcards are also useful for finding text that contains a particular phrase. If A2:A100 contains descriptions and E1 contains the word to search for, =COUNTIF(A2:A100,"*"&E1&"*") counts cells containing that text anywhere in the cell. The asterisks allow characters before and after the search term. COUNTIF is not case-sensitive, so this formula treats uppercase and lowercase versions as a match.

Text values are not the same as displayed text

COUNTIF with "*" looks at the cell’s value, not simply how the value appears on screen. A number formatted to look like a date or a currency amount remains numeric and is not counted as text. Similarly, a number with a custom format that adds a label may still be a numeric value. By contrast, a value entered with a leading apostrophe, such as '0042, is stored as text and can be counted.

Formula results follow the same principle. If a formula returns a text string, the result is text and can match the wildcard criterion. A formula that returns a number produces a numeric result, even if the sheet formats that result to look like text. When a count seems wrong, select a sample cell and inspect the formula bar and formatting; the visual appearance alone may not reveal its underlying type.

Spaces are characters, too. A cell that appears blank but contains one or more spaces may be counted by the asterisk criterion. A space-only cell can therefore make a count larger than the number of visibly meaningful entries. If your data comes from a form, import, or copy-and-paste operation, check for spaces and empty-string formulas before treating the count as a count of completed answers.

Choose the right range and counting rule

Use a range that covers the records you intend to count. A fixed range such as A2:A500 is clear and can be efficient for a known dataset, but new rows below row 500 will not be included. For a column that grows over time, you can use A2:A, which starts at row 2 and continues down the column. Avoid including a header if it should not count: a text heading in A1 is itself a text value.

Before relying on a count, decide what qualifies as a text cell:

Use "*" to count cells with text values, including entries that contain spaces or digits alongside letters.
Use "?*" to count text values with at least one character, excluding empty-string results.
Use a more specific wildcard criterion, such as "*error*", to count cells containing a phrase anywhere in their text.
Check whether text-looking numbers should count. A value stored as text is different from the same number stored numerically.

These criteria answer different questions. If a report is meant to count all nonempty cells, including numbers, dates, and text, COUNTIF with a text wildcard is not the right rule. With data in A2:A20, use =COUNTA(A2:A20) to count cells containing values. COUNTA is broader than the text count: it includes numbers and other nonblank values, and formulas that return an empty string can affect results. Choose the function based on what the report is supposed to measure.

Count text that meets additional conditions

You can combine a text criterion with another condition using COUNTIFS. For instance, if names or notes are in A2:A20 and the corresponding status is in B2:B20, this formula counts text entries in column A only when the status is Done: =COUNTIFS(A2:A20,"*",B2:B20,"Done"). The assumed layout is paired records in rows 2 through 20; the result is the number of rows where column A matches the text wildcard and column B equals Done.

COUNTIFS applies all its criteria to the same row, so the ranges should cover corresponding rows and have the same dimensions. If you use A2:A20 for the text criterion, use B2:B20 for the status criterion, not a shifted range such as B3:B21. For a status that may contain additional words, use a wildcard criterion such as "*Done*", bearing in mind that this can also match a longer word or phrase that happens to contain Done.

For a text count restricted to one category, set one criterion to the category name. For example, if A2:A100 contains comments and B2:B100 contains departments, =COUNTIFS(A2:A100,"?*",B2:B100,"Sales") counts nonempty text comments in rows assigned to Sales. This is often more useful than counting an entire column when a sheet contains several teams, time periods, or record types.

Common reasons the count looks wrong

If the result is lower than expected, check whether some apparent text is actually numeric. Imported IDs, for example, may be numbers in some rows and text in others. Also check that your range includes the rows with data and does not stop above newly added entries. A cell containing a formula that returns a number will not count as text, even if its displayed format resembles a label.

If the result is higher than expected, look for spaces, formulas returning empty strings, or a header included in the range. A space is not visually obvious, and a formula can make a cell appear blank without making it a truly empty cell. Try the "?*" criterion when empty strings are part of the issue, then inspect suspicious cells individually. It is not a general cleanup tool: spaces-only entries still contain characters and can match.

Another common confusion is expecting COUNTIF to count only cells made entirely of letters. It does not. The wildcard matches text containing punctuation, digits, spaces, or symbols as well as ordinary words. If your rule is more specific—for example, count only values matching a particular pattern—define that rule separately rather than assuming "*" means “letters only.”

Finally, spreadsheet locale settings can affect how formulas are entered. Some locales use semicolons rather than commas between function arguments. If Sheets reports a formula parse error, check the spreadsheet’s locale under File > Settings and use the argument separator expected by that locale. The wildcard criteria themselves remain in quotation marks.

Frequently Asked Questions

Does COUNTIF with an asterisk count numbers?
No. With a criterion of "*", COUNTIF matches text values, not numeric values. A number stored as text can be counted, while a numeric value that is merely formatted to look like text remains a number.

How do I count cells that contain a specific word?
If the text is in A2:A100 and the word is in E1, use =COUNTIF(A2:A100,"*"&E1&"*"). The surrounding wildcards find the search text within a cell. The match is case-insensitive.

How can I count text but ignore cells that look blank?
With the cells to check in A2:A20, use =COUNTIF(A2:A20,"?*") to require at least one character. This excludes genuinely empty cells and empty-string results, but a cell containing spaces still has characters and may be counted.

What is the difference between COUNTIF and COUNTA?
COUNTIF with a text wildcard counts cells matching a text condition. COUNTA counts nonempty values of different types, including numbers and text. Use COUNTIF when the question is specifically about text; use COUNTA when you want a broader count of populated cells.

Does the formula work in Excel too?
Excel also supports COUNTIF and the * and ? wildcards, so the basic approach is similar. This page describes Google Sheets in a browser; menu details and locale-specific separators can differ between spreadsheet applications and settings.

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.