Count Cells with Partial Text Wildcard in Excel and Google Sheets
Purpose
Counting cells that match a partial text pattern — starts with a prefix, ends with a suffix, or fits a…
Counting cells that match a partial text pattern — starts with a prefix, ends with a suffix, or fits a fixed-width format — is where COUNTIF wildcards show their real power. Counting product codes that begin with PRD- to measure range coverage. Counting URLs that end with .com versus international domains. Counting postal codes that fit a specific country's format. Every one of these uses the same COUNTIF function you already know, but with carefully placed asterisks and question marks as the criteria. The asterisk means "any text of any length" and the question mark means "exactly one character". Combined with the count cells containing specific text pattern for substring searches, wildcard-based counting covers most text-matching problems you will encounter in a spreadsheet. The formulas look slightly cryptic the first time you see them but once you have built a handful of them the mental model is obvious.
Syntax
✓ All versions
✓ Google Sheets
= Same syntax
=COUNTIF(range, "pattern")
Patterns use * for any text and ? for one character.
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The cells to examine. Can be a specific range like A2:A100 or a column reference. Only text cells are evaluated by the wildcard pattern — numbers and blanks are skipped. |
| criteria | Required | The wildcard pattern as a quoted string. Use * for any sequence of characters, ? for exactly one character, and ~ to treat a wildcard character as a literal. Pattern matching is case-insensitive. |
How it works
The asterisk wildcard matches any sequence of characters, including zero characters (an empty string). Placing an asterisk at the end of the criteria — "PRD-*" — anchors the match to the start, counting only cells that begin with PRD-. Placing an asterisk at the beginning — "*.com" — anchors the match to the end, counting only cells that end with .com. Asterisks on both sides match the text anywhere, which is the substring-match pattern used by the count cells containing specific text formula. The question mark wildcard matches exactly one character, which is useful for structured data like postal codes or product IDs with a known format. You can combine asterisks and question marks in the same pattern — "AB??-*" matches any code starting with AB, then exactly two characters, then a hyphen, then anything. All wildcard matching in COUNTIF is case-insensitive, and both wildcards work the same way in the COUNTIF function and the COUNTIFS function. If your data genuinely contains an asterisk or question mark that you want to match literally, prefix it with a tilde: "~*" matches a real asterisk character.
Examples
1
Count how many product codes start with the prefix PRD- to measure how many items belong to the Products category.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Code | Count PRD- |
| 2 | 1 | PRD-001 | =COUNTIF(B2:B11,"PRD-*") |
| 3 | 2 | CAT-002 | |
| 4 | 3 | PRD-003 | 6 |
| 5 | 4 | REG-004 | |
| 6 | 5 | PRD-005 | |
| 7 | 6 | PRD-006 | |
| 8 | 7 | CAT-007 | |
| 9 | 8 | REG-008 | |
| 10 | 9 | PRD-009 | |
| 11 | 10 | PRD-010 |
Row 2: 6
— Six codes start with PRD-: rows 1, 3, 5, 6, 9, and 10 (PRD-001, PRD-003, PRD-005, PRD-006, PRD-009, PRD-010). The CAT- and REG- codes are not matched.
The asterisk at the end means "followed by anything", so the match is anchored to the start of the cell. Placing the asterisk at the beginning ("*PRD-*") would find PRD- anywhere in the cell, which would also count codes that contain PRD- in the middle.
2
Count how many website URLs end with the .com top-level domain using the ends-with wildcard pattern.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Domain | Count .com |
| 2 | 1 | site.com | =COUNTIF(B2:B11,"*.com") |
| 3 | 2 | site.org | |
| 4 | 3 | mysite.com | 6 |
| 5 | 4 | site.net | |
| 6 | 5 | thesite.com | |
| 7 | 6 | site.co.uk | |
| 8 | 7 | other.com | |
| 9 | 8 | site.io | |
| 10 | 9 | example.com | |
| 11 | 10 | test.com |
Row 2: 6
— Six domains end with .com: site.com, mysite.com, thesite.com, other.com, example.com, and test.com. The .org, .net, .co.uk, and .io domains are not matched.
The asterisk at the start matches any characters before the ending, and the literal .com at the end of the pattern anchors the match. Without the leading asterisk, ".com" would only match a cell that is exactly the string .com with nothing else.
3
Count how many UK-style postal codes match the fixed-format pattern AB? ?CD, where each ? matches exactly one character.
| A | B | C | |
|---|---|---|---|
| 1 | Row | Code | Count Pattern |
| 2 | 1 | AB1 2CD | =COUNTIF(B2:B11,"AB? ?CD") |
| 3 | 2 | EF3 4GH | |
| 4 | 3 | XX5 6YY | 5 |
| 5 | 4 | AB7 8CD | |
| 6 | 5 | AB9 0CD | |
| 7 | 6 | EF1 2GH | |
| 8 | 7 | AB3 4CD | |
| 9 | 8 | XX5 6ZZ | |
| 10 | 9 | AB6 7CD | |
| 11 | 10 | EF8 9GH |
Row 2: 5
— Five codes match the AB? ?CD pattern: AB1 2CD, AB7 8CD, AB9 0CD, AB3 4CD, and AB6 7CD. Codes starting with EF or XX do not match, and neither do any AB codes whose format differs.
Each ? matches exactly one character — no more, no less. The pattern enforces AB, one character, space, one character, CD — total 7 characters. This is useful whenever you need to count entries that fit a structured fixed-width format like tracking numbers, national identity codes, or reference codes.
Common use cases
1. Count product codes starting with a category prefix for inventory reporting
=COUNTIF(A2:A500,"PRD-*")
2. Count URLs ending with a specific top-level domain for audience analysis
=COUNTIF(B2:B1000,"*.com")
3. Count postal codes fitting a fixed format like UK outward codes using question marks
=COUNTIF(C2:C200,"AB? ?CD")
4. Count filenames with a specific extension such as .pdf in a document list
=COUNTIF(D2:D500,"*.pdf")
5. Count SKUs with a specific first two characters and any ending using mixed wildcards
=COUNTIF(E2:E1000,"??-*")
Common errors
Count is 0 when items clearly match the pattern
The pattern has an extra space or the wrong wildcard character. For example, "PRD- *" with a space before the asterisk requires an actual space after PRD-.
Fix: Double-check the pattern letter by letter. For start-of-string matches, the wildcard goes only at the end with no space.
Count is higher than expected
The pattern has wildcards on both sides, so it matches the text anywhere rather than at a specific position. For example, "*PRD-*" matches PRD- even in the middle of a string.
Fix: Remove the leading asterisk for starts-with matches or the trailing asterisk for ends-with matches. Position the wildcards intentionally.
The pattern contains a literal asterisk or question mark in the data but it is being treated as wildcard
Wildcards ? and * are always interpreted as special characters by COUNTIF unless escaped.
Fix: Prefix the wildcard character with a tilde in the pattern. "Code ~?" matches the literal text "Code ?" with an actual question mark character.
Tips and variations
Anchor patterns to the start with a trailing asterisk only
For starts-with counts, put the search text at the beginning and a single asterisk at the end — no leading asterisk. This is much more precise than asterisks on both sides and gives you cleaner results with the COUNTIF function.
=COUNTIF(A2:A100,"PRD-*")
Anchor patterns to the end with a leading asterisk only
For ends-with counts, put the asterisk first and the search text at the end. This is the mirror pattern of starts-with and is equally useful for things like file extensions or top-level domains.
=COUNTIF(A2:A100,"*.pdf")
Combine ? and * for fixed-width prefixes with variable endings
Use ? characters to enforce a fixed-length prefix and * to allow any ending. This is how I validate ID format compliance in audit reports — along with the count cells containing specific text pattern for broader text checks.
=COUNTIF(A2:A100,"???-*")
Excel vs Google Sheets
Excel vs Google Sheets
Wildcard-based COUNTIF works identically in Excel 2003 and newer, Excel 365, and every version of Google Sheets. The * and ? wildcards and the ~ escape character behave the same in both applications. Formulas copy between the two without any changes.
Related reading
Frequently asked questions
COUNTIF supports two wildcards: an asterisk (*) matches any sequence of characters including an empty string, and a question mark (?) matches exactly one character. So "PRD-*" matches any text starting with PRD-, and "AB? ?CD" matches text with AB, one character, a space, one character, and CD. Both wildcards work in Excel and Google Sheets.
Put the search text first followed by an asterisk: =COUNTIF(A2:A100,"PRD-*") counts cells starting with PRD-. The asterisk on the right end says "anything can follow", which is what "starts with" means in wildcard terms. Do not put an asterisk at the start — that would match the text anywhere, not only at the beginning.
Put the asterisk first followed by the search text: =COUNTIF(A2:A100,"*.com") counts cells ending with .com. The asterisk on the left says "anything can come before", which is what "ends with" means in wildcard terms.
The question mark matches exactly one character — no more, no less. Use it for fixed-width patterns like postal codes, SKU codes, or phone number formats. For example, "??-??-??" matches a pattern like 12-34-56 or AB-CD-EF with exactly two characters in each segment separated by hyphens.
Put a tilde (~) before the character to treat it as a literal: "~*" matches an actual asterisk, and "~?" matches an actual question mark. This is only needed when your data genuinely contains those characters and you do not want them to be treated as wildcards.