Excel COUNTIF Starts With: Wildcard Formula

Formulas & functions · Updated November 22, 2026

To count cells in Excel that start with specific text, use the COUNTIF function with a wildcard: =COUNTIF(range,"text*"). For example, =COUNTIF(A2:A10,"ap*") counts every cell in A2:A10 whose value begins with “ap”. The asterisk (*) matches any number of characters after the text, so the formula catches “apple”, “april”, and “application” alike.

How COUNTIF Handles Text Matching

COUNTIF is a core Excel function that counts cells in a range meeting a single condition. Its syntax is =COUNTIF(range, criteria). When the criteria is text, Excel treats it as a case-insensitive exact match by default. To match a pattern rather than an exact string, you add wildcard characters:

  • Asterisk (*) – matches any sequence of characters (including none).
  • Question mark (?) – matches exactly one character.
  • Tilde (~) – escapes a wildcard so you can match a literal asterisk or question mark.

So =COUNTIF(A2:A10,"ap*") finds any cell that starts with “ap”. The asterisk after the text is what makes it a “starts with” test. Without the asterisk, COUNTIF would look for an exact match of “ap” only.

Practical Examples of Starts-With Counting

Assume you have a product list in cells A2:A20 with values like “Apple”, “Banana”, “Apricot”, “Avocado”, “Grape”. To count products starting with “Ap”, use:

=COUNTIF(A2:A20,"Ap*")

This returns 2 (Apricot and Avocado) because matching is case-insensitive. For a more dynamic approach, you can reference a cell containing the prefix. Put “Ap” in cell B1 and use:

=COUNTIF(A2:A20,B1&"*")

The ampersand (&) concatenates the prefix with the asterisk. This lets you change the prefix without editing the formula. If you need to count cells that start with any of several prefixes, you can sum multiple COUNTIF results:

=COUNTIF(A2:A20,"Ap*")+COUNTIF(A2:A20,"Ba*")

That counts cells starting with either “Ap” or “Ba”.

Using Wildcards with Other Text Functions

COUNTIF is not the only function that accepts wildcards. SUMIF and AVERAGEIF also support them in their criteria arguments. For example, to sum sales in column B where the product in column A starts with “Ap”, use:

=SUMIF(A2:A20,"Ap*",B2:B20)

Similarly, COUNTIFS can count rows meeting multiple conditions, and it supports wildcards too. For instance, to count rows where column A starts with “Ap” and column B is greater than 100, use:

=COUNTIFS(A2:A20,"Ap*",B2:B20,">100")

Note that wildcards work only with text criteria, not with numeric criteria. If your range contains numbers formatted as text, they still match text patterns, but true numeric values do not respond to asterisks.

Case Sensitivity and Exactness

COUNTIF is not case-sensitive. =COUNTIF(A2:A10,"ap*") matches “Apple”, “APRICOT”, and “april” alike. If you need a case-sensitive count, you must switch to SUMPRODUCT with the EXACT function. For example, to count cells that start with lowercase “ap” exactly, use:

=SUMPRODUCT(--(LEFT(A2:A10,2)="ap"))

This works because LEFT extracts the first two characters and EXACT is implied by the equality operator in SUMPRODUCT, which is case-sensitive. However, this only works if the prefix length is fixed. For variable-length prefixes, you would need a more complex array formula. In practice, most users rely on COUNTIF because case rarely matters in data entry.

Common Problems and Solutions

Users often run into a few pitfalls when using COUNTIF with starts-with patterns:

  • Leading spaces – If cells contain invisible spaces before the text, “Ap*” won’t match. Use =TRIM(A2) in a helper column or use =COUNTIF(A2:A10,"*Ap*") to match anywhere, but that’s not a true starts-with. Better: clean data with TRIM and then apply COUNTIF.
  • Wildcards not working – Ensure the criteria is in quotes. =COUNTIF(A2:A10,Ap*) is invalid. Also, if you reference a cell, concatenate with &.
  • Counting blanks – =COUNTIF(A2:A10,"*") counts all non-empty text cells, including those with spaces. To exclude spaces, use =COUNTIF(A2:A10,"?*") which requires at least one character.
  • Literal asterisk – If your data contains an actual asterisk at the start, use =COUNTIF(A2:A10,"~**") to count cells starting with a literal asterisk. The tilde escapes the first asterisk.

Alternatives to COUNTIF for Starts-With

While COUNTIF is the simplest, other functions can achieve the same result, each with trade-offs:

  • SUMPRODUCT with LEFT – =SUMPRODUCT(--(LEFT(A2:A10,3)="app")) counts cells whose first 3 characters are “app”. This is case-sensitive and requires a fixed prefix length.
  • FILTER with COUNT – In Microsoft 365, =ROWS(FILTER(A2:A10,LEFT(A2:A10,3)="app")) returns the count of rows that match. This is more flexible but longer.
  • Power Query – For large datasets, you can load data into Power Query, add a conditional column that checks if text starts with a prefix, and then count. This is overkill for a simple count but useful for complex transformations.

For most users, COUNTIF is the fastest and most readable solution. It works in all Excel versions, including Excel for the web and Mac, with identical syntax.

Frequently Asked Questions

Does COUNTIF work with numbers that start with a digit? No. COUNTIF treats numeric values as numbers, not text, so wildcards are ignored. To count numbers starting with a specific digit, you would need to convert them to text first, e.g., =COUNTIF(TEXT(A2:A10,"0"),"1*") as an array formula (enter with Ctrl+Shift+Enter in older Excel). In Microsoft 365, it works as a dynamic array.

How do I count cells that start with a letter but not a specific one? Use =COUNTIF(range,"[!A]*") to count cells that start with any character except “A”. The brackets define a character set, and the exclamation mark negates it. This is a lesser-known wildcard feature.

Can I use COUNTIF to count cells that start with a number and end with a letter? Yes, combine wildcards: =COUNTIF(range,"1*Z") counts cells starting with “1” and ending with “Z”. The asterisk in the middle matches any characters.

Why does my COUNTIF return 0 when I see matching text? Check for leading or trailing spaces, non-breaking spaces (CHAR(160)), or that the range actually contains text, not numbers. Use =TRIM(A2) to clean, and consider using =COUNTIF(range,"*"&prefix&"*") to see if the text appears anywhere.

Is there a way to make COUNTIF case-sensitive? No, COUNTIF is always case-insensitive. For case-sensitive starts-with, use SUMPRODUCT with EXACT as shown earlier.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.