Google Sheets COUNTIF Wildcards: How to Match Text

Yes. Google Sheets COUNTIF supports the asterisk (*) wildcard to match any number of characters and the question mark (?) to match one character; use a tilde (~) before either symbol when you want it treated literally. For example, with product names in A2:A20, =COUNTIF(A2:A20,"*apple*") counts cells containing “apple” anywhere in the text.

What COUNTIF wildcards do

COUNTIF checks a range against one criterion and returns the number of cells that meet it. A wildcard makes a text criterion flexible: instead of requiring a cell to equal a complete phrase, you can describe a pattern. That is useful for lists where entries share a word, beginning, ending, or character pattern but are not identical.

The two wildcard characters have different jobs:

* stands for zero or more characters. Put it before a word to find text ending in that word, after a word to find text beginning with it, or on both sides to find it anywhere.
? stands for exactly one character. Use several question marks to specify the number of characters you want to match.

For example, “app*” can match “apple” and “application,” while “app??” can match “apply” but not “apple pie.” Wildcards are part of the criterion text, not special characters typed into the cells. COUNTIF also treats text matching as case-insensitive, so a criterion for “apple” can match “Apple.”

Match text at the beginning, end, or anywhere

Where you place the asterisk determines what the rest of the criterion must match. Suppose A2:A9 contains Apple, green apple, pineapple, pear, Apple pie, grape, a blank cell, and APPlE. These patterns illustrate the difference:

"app*" matches text that begins with “app,” such as Apple and Apple pie.
"*apple" matches text that ends with “apple,” such as green apple and pineapple.
"*apple*" matches text containing “apple” anywhere, including Apple pie.

With those example entries in A2:A9, the formula =COUNTIF(A2:A9,"*apple*") returns 5: Apple, green apple, pineapple, Apple pie, and APPlE. The wildcard at each end allows other characters before or after the word, but the word itself must still appear in sequence. A match does not require “apple” to be a separate word, which is why “pineapple” is included.

If you only want names that start with a prefix, use the prefix followed by an asterisk. With names in A2:A20, =COUNTIF(A2:A20,"Sam*") counts entries such as “Sam,” “Sam Lee,” and “Samantha,” but not “Alex Sam.” Add the leading asterisk when the prefix may occur anywhere rather than only at the start.

Use ? to match a specific number of characters

The question mark is useful when a value has a fixed character position or length. In the A2:A9 sample above, "?????" matches cells containing exactly five characters: Apple, grape, and APPlE. The result is 3. It will not match “pear” because that has four characters, or “green apple” because it has more.

You can combine question marks with ordinary text and asterisks. For example, “A?7” describes a three-character pattern: an A, any one character, and a 7. “AB??*” requires the text to begin with AB and have at least two more characters, followed by any amount of additional text. Remember that “?” represents one character, not one word, and punctuation or spaces can occupy that position too.

These patterns can help check codes with a known format, but they do not validate every rule of a code system. A pattern might count a value with the right character positions even if its characters are otherwise invalid. For stricter validation, use additional checks suited to the data rather than relying on a wildcard alone.

Escape a literal asterisk or question mark

If the cell contains an actual asterisk or question mark, COUNTIF would normally interpret that symbol as a wildcard. Put a tilde immediately before it in the criterion to search for the symbol itself: “~*” represents a literal asterisk, and “~?” represents a literal question mark.

For example, with codes in B2:B7, including A*12 and A*99, the formula =COUNTIF(B2:B7,"*~**") counts cells containing a literal asterisk anywhere in the code. The first and last asterisks allow any text around the middle, escaped asterisk; the result is 2 for those sample values. Without the tilde, the asterisks in the criterion would act as wildcards instead of identifying the literal symbol.

To match a cell whose entire value is just an asterisk, use the criterion “~*” without wildcard characters around it. The same principle applies to a literal question mark. The tilde is the escape character in this context; it is not itself a character the criterion asks the cell to contain.

Build a criterion from another cell

You do not have to type a search word directly into the formula. You can combine wildcard characters with a cell reference using the ampersand (&), which joins text. This makes a small search box convenient: change the search term in one cell and the count updates.

With product names in A2:A20 and the search term in D1, =COUNTIF(A2:A20,"*"&D1&"*") counts names containing the text entered in D1. If D1 contains “apple,” it counts matches such as “green apple” and “pineapple.” If D1 is empty, the criterion becomes “**”; that can count all nonblank text entries, so consider whether an empty search should instead display a prompt or a blank result.

When the value in the criterion cell itself contains * or ?, those characters remain wildcard characters when joined into the criterion. If users need to search for literal wildcard symbols, escape them in the criterion or use a separate approach that handles the input as literal text. This distinction matters in user-entered search boxes and imported codes.

Combine wildcard matching with other criteria

COUNTIF accepts one criterion. If you need multiple conditions to be true at the same time—for example, a product name pattern and a region—use COUNTIFS, which evaluates a criterion for each supplied range. The ranges need to correspond in size and position.

With product names in A2:A20 and regions in B2:B20, =COUNTIFS(A2:A20,"*pen*",B2:B20,"West") counts rows where the product name contains “pen” and the region is West. A row must meet both conditions to contribute to the result. COUNTIFS can also use wildcard criteria for more than one text field.

For a single numeric threshold, COUNTIF uses comparison criteria such as “>10”; wildcards are for text-pattern matching, not a substitute for numeric comparisons. When a comparison value comes from a cell, the operator is typically joined to that reference as text. Keep the job of each criterion clear: use wildcard matching for text patterns and comparison operators for numeric or date conditions.

Common problems and ways to troubleshoot

A result of zero does not always mean the visible text is absent. First check whether the formula points to the intended range and whether the criterion has the right wildcard placement. “apple*” only matches at the beginning; it will not find “green apple.” To search anywhere, place an asterisk on both sides.

Check spaces: A leading or trailing space in a cell can affect patterns that must match the beginning or end. Imported data may include spaces that are hard to see.
Check punctuation: Hyphens, apostrophes, and other characters are part of the text. A pattern expecting a space will not match a hyphen in the same position.
Check literal symbols: Escape an actual * or ? with ~. Otherwise, COUNTIF interprets it as a wildcard.
Check blanks: A wildcard pattern intended to find text does not make an empty cell a text match. If blank cells are the target, use a blank criterion rather than a wildcard search.
Check the source values: A cell that looks like text may be a number or date stored as a value. Wildcard criteria are most useful for text patterns, so confirm what the cells contain when results seem surprising.

Also distinguish “contains” from “equals.” The criterion “apple” counts cells equal to apple (with text matching that is not case-sensitive); it does not mean “contains apple.” Use “*apple*” for a contains-style match. Conversely, that contains pattern also matches longer words such as “pineapple,” which may or may not be desirable.

Google Sheets and Excel wildcard behavior

Google Sheets COUNTIF uses the familiar asterisk, question mark, and tilde escape convention. Excel COUNTIF uses the same basic wildcard characters, so many simple COUNTIF formulas transfer between the two spreadsheet programs. However, this page’s examples and instructions are for Google Sheets in a web browser; the interface, formula separators under some regional settings, and other spreadsheet features can differ.

In Google Sheets, formulas are entered directly into a cell or the formula bar. If your spreadsheet’s locale uses a different argument separator, Sheets may require semicolons instead of commas; follow the separator used by formulas in that file. This is a locale setting issue, not a different wildcard meaning. The criteria still need quotation marks around typed text and the wildcard pattern.

Frequently Asked Questions

Does COUNTIF in Google Sheets support wildcards?
Yes. Use * for zero or more characters and ? for exactly one character. Use ~* or ~? when you want to match a literal asterisk or question mark.

How do I count cells containing a word anywhere?
Put an asterisk before and after the word in the criterion. For example, with comments in C2:C50, =COUNTIF(C2:C50,"*late*") counts text containing “late,” including longer words that contain those letters.

Is COUNTIF with wildcards case-sensitive?
No. COUNTIF text matching is case-insensitive, so a criterion for “west” can match “West” or “WEST.” Use a different approach if capitalization must be distinguished.

How do I count a literal asterisk in a cell?
Escape it with a tilde. Use “~*” to match a cell whose value is an asterisk, or surround the escaped symbol with wildcards, as in “*~**,” to find it within longer text.

Can COUNTIF match a word only at the start of a cell?
Yes. Put the word first and add an asterisk after it, such as “pre*.” That matches text beginning with “pre”; it does not match the same letters appearing only later in the cell.

Why does my wildcard count include part of a longer word?
A wildcard pattern searches for the character sequence, not necessarily a complete word. “*pen*” can match “pen” and “open.” If whole-word matching matters, use a method that checks word boundaries rather than assuming COUNTIF wildcards distinguish words.

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.