Google Sheets: Count Cells That Contain Text with COUNTIF

To count cells that contain text in Google Sheets, use =COUNTIF(A2:A100,"*"), replacing A2:A100 with your range. The asterisk is a wildcard that matches text strings, so this counts text entries while excluding ordinary numbers, blank cells, dates, and Boolean values.

What COUNTIF Counts

COUNTIF checks a range against one condition and returns the number of cells that meet it. In this case, the condition is the wildcard *, which stands for any sequence of characters. Because the criterion is a text wildcard, it matches text content rather than every kind of value in the range.

For example, with names or status labels in A2:A100, the formula counts entries such as North, pending, 007 stored as text, and needs review. It does not count the numeric value 7, a date stored as a date, or TRUE. A number that merely looks like text is still treated according to how it is stored: 007 entered or imported as text can count, while the numeric value 7 formatted to display as 007 does not.

The formula counts cells, not individual words or characters. A cell containing the sentence “needs review” contributes one to the total, just like a cell containing a single word. COUNTIF is also not case-sensitive, so it treats Pending and pending as matches in the same way.

Use COUNTIF with a Range

With the values you want to inspect in A2:A100, enter this formula in an unused cell:

=COUNTIF(A2:A100,"*")

Replace A2:A100 with your actual range. For instance, if you are checking product descriptions in column C from row 2 through row 250, use =COUNTIF(C2:C250,"*"). Keeping the range limited to the relevant rows makes the formula easier to understand and can avoid scanning a large unused area.

Here is a small example. Suppose A2:A9 contains Alpha, the number 12, pending, a blank cell, 007 stored as text, a date, TRUE, and needs review. With that layout, =COUNTIF(A2:A9,"*") returns 4: Alpha, pending, 007, and needs review are the four nonempty text entries. The date and 12 are numeric values, TRUE is a Boolean value, and the blank contributes nothing.

In Google Sheets, enter the formula directly in a cell and press Enter. You can also select a cell, type an equals sign, and build the formula in the formula bar. Google Sheets uses commas between arguments in this example. The same basic COUNTIF syntax is also available in Excel, although this article describes Google Sheets in a web browser.

Count Text in a Larger or Changing List

If new rows are regularly added to a list, a range that includes spare rows can save you from editing the formula each time. For example, with entries in column A beginning at row 2, the formula =COUNTIF(A2:A,"*") checks from A2 to the bottom of the column. It still counts matching text cells rather than counting every row in that range.

A full-column range such as A:A is also possible, but it includes the header and every other row in the column. If the header is text, it will be counted. Starting at A2 avoids that particular problem when A1 is a header. For a large spreadsheet or a formula copied many times, a bounded range can be clearer and more efficient to maintain.

If the data is arranged in several columns and you want one combined count, you can use a rectangular range. With text values anywhere in A2:C100, =COUNTIF(A2:C100,"*") counts matching cells across all three columns. It does not count a row once merely because the row contains text; each matching cell contributes separately.

Count Text That Contains a Particular Word

To count only cells containing a word or phrase, put that text between asterisks. With comments in A2:A100, use =COUNTIF(A2:A100,"*urgent*") to count entries that contain urgent anywhere in the cell. It can match urgent at the beginning, middle, or end of a longer string, and it is not case-sensitive.

You can also refer to a cell containing the search term. If B1 contains urgent and comments are in A2:A100, use =COUNTIF(A2:A100,"*"&B1&"*"). The ampersands join the wildcard characters to the contents of B1. This makes it easier to change the term without editing the formula.

Wildcards have special meanings: an asterisk represents any number of characters, and a question mark represents one character. If you need to search for an actual asterisk or question mark in cell content, prefix that character with a tilde in the criterion. For example, the tilde is the escape character for COUNTIF wildcard searches. This matters when a spreadsheet contains codes or notes that literally use those symbols.

Text Cells, Nonblank Cells, and Formula Results

A common source of confusion is the difference between counting text and counting cells that are not blank. COUNTIF with "*" is aimed at text. COUNTA, by contrast, counts cells containing values of different types, including numbers and Boolean values, as well as text. If a column mixes names, amounts, dates, and status flags, COUNTA will usually produce a larger total than COUNTIF with a text wildcard.

There is also a difference between a truly empty cell and a formula that returns an empty string. A formula such as ="" displays nothing, but the cell contains a formula. Depending on the function and the exact criterion, formula-generated empty strings can behave differently from untouched blank cells. If your range includes such formulas and the distinction matters, inspect a few affected cells rather than assuming that a visually blank cell is genuinely empty.

For a count of text values that have at least one character, excluding empty-string results, use an array-based check. With the values in A2:A100 and no errors in that range, this formula counts cells that are text and have a length greater than zero:

=SUMPRODUCT(N(ISTEXT(A2:A100)*(LEN(A2:A100)>0)))

This is more explicit than the simple COUNTIF option and is useful when formulas returning empty strings are part of the data. A cell containing spaces has a length greater than zero, however, so this formula still counts it. If spaces should be treated as empty, clean the source data or adapt the test to trim whitespace, taking care not to change values that are meaningful in your dataset.

Choose the Right Formula for the Question

Before choosing a formula, decide what “contains text” means for your task. Different questions call for different counting rules:

Count nonempty text entries: use =COUNTIF(A2:A100,"*").
Count every nonempty value: use =COUNTA(A2:A100), remembering that it includes numbers and other value types.
Count text containing a phrase: use =COUNTIF(A2:A100,"*phrase*"), replacing phrase with the text to find.
Count text with at least one character while excluding empty-string formulas: use the SUMPRODUCT and ISTEXT approach described above.

These formulas answer related but different questions. For example, in a list of customer responses, COUNTIF with a wildcard can give the number of written responses, while COUNTA can also include numeric codes or Boolean flags. If the purpose is reporting, make the counting rule clear so that readers know whether values such as a dash, a space, or a numeric identifier count as an answer.

Common Reasons the Count Looks Wrong

If the result is lower than expected, the cells may contain numbers stored as numbers rather than text, dates, or Boolean values. Formatting a number to look like text does not change its underlying type. A cell displaying 0012, for example, may still hold the numeric value 12 with a custom number format; the wildcard count does not treat that display format as text.

If the result is higher than expected, look for cells containing spaces, stray characters, or formula outputs. A cell that appears empty may contain a space, and a cell may contain an invisible character copied from another source. Those are different from a truly empty cell and may be treated as text. Google Sheets’ Edit > Find and replace can help locate or replace repeated unwanted characters, but check the range and replacement settings before applying changes.

Also check the range boundaries. An included header, notes column, or hidden row can add a match. Hidden rows are still part of the range and are not automatically excluded by COUNTIF. If you need a count that changes according to filters or visible rows, COUNTIF alone may not represent that requirement; the criteria need to reflect how your sheet is structured.

Finally, confirm that the formula uses straight quotation marks around the asterisk and that the range is correct. A curly quotation mark copied from formatted text is not the same character as a standard formula quote. If you are uncertain about a specific cell, use a small test range containing that cell and a few known examples to see how the formula classifies them.

Frequently Asked Questions

Does COUNTIF with an asterisk count numbers?
No. =COUNTIF(A2:A100,"*") matches text, not ordinary numeric values. A number stored as text can be counted, while a number displayed with a text-like format is still numeric.

Does COUNTIF distinguish uppercase and lowercase?
No. COUNTIF is case-insensitive for ordinary text matching. If case-sensitive counting is essential, use a different approach based on exact text comparisons rather than relying on this wildcard criterion.

How do I count cells containing a specific word?
With the text in A2:A100, use =COUNTIF(A2:A100,"*word*"), replacing word with the term you want. The asterisks allow other characters before or after the term.

Why is a cell that looks blank included?
It may contain a space, another invisible character, or a formula that returns an empty string. Check the cell’s formula bar and contents; visual appearance alone does not establish that a cell is truly empty.

Can I count text across multiple columns?
Yes. Use a rectangular range, such as =COUNTIF(A2:C100,"*"), to count matching text cells throughout those columns. The result is a cell count, not a count of rows with at least one text entry.

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.