Excel COUNTIF Contains Partial Text: Formula & Examples

Formulas & functions · Updated November 22, 2026

Yes, you can use COUNTIF for partial text in Excel by combining it with wildcard characters: asterisk (*) matches any sequence of characters, and question mark (?) matches a single character. The basic formula is =COUNTIF(range,"*text*"), which counts every cell in the range that contains the specified text anywhere within it.

How Wildcards Work in COUNTIF

Excel’s COUNTIF function accepts three wildcard characters that let you match partial text without writing complex formulas. The asterisk (*) represents zero or more characters, the question mark (?) represents exactly one character, and the tilde (~) escapes a wildcard so you can search for a literal asterisk or question mark.

For example, with product names in A2:A10, the formula =COUNTIF(A2:A10,"*apple*") counts cells containing “apple” anywhere—”apple pie”, “pineapple”, or just “apple”. The formula =COUNTIF(A2:A10,"a?e") counts three-letter words starting with “a” and ending with “e”, like “are” or “ate”, but not “ae” or “able”.

Basic Formula for Partial Text Match

The most common need is counting cells that contain a specific substring, regardless of position. The formula structure is:

=COUNTIF(range,"*substring*")

Assume you have a list of email addresses in B2:B20. To count how many contain “@gmail.com”, use =COUNTIF(B2:B20,"*@gmail.com*"). If the data includes “user@gmail.com” and “contact@GMAIL.com”, note that COUNTIF is not case-sensitive, so both are counted. The result for a range with three such addresses would be 3.

For text that starts with a specific prefix, place the asterisk only at the end: =COUNTIF(A2:A10,"prod*") counts cells beginning with “prod”. For text ending with a suffix, put the asterisk at the start: =COUNTIF(A2:A10,"*2024").

Using Cell References for the Search Text

Hardcoding the substring works for one-off checks, but you often want to reference a cell containing the search term. To do that, concatenate the wildcards with the cell reference using the ampersand (&) operator.

With the search term in D1, the formula becomes =COUNTIF(A2:A10,"*"&D1&"*"). If D1 contains “apple”, this is equivalent to =COUNTIF(A2:A10,"*apple*"). This approach lets you change the search term without editing the formula each time.

Counting Partial Matches with Multiple Criteria

When you need to count partial matches that also meet other conditions, use COUNTIFS instead of COUNTIF. COUNTIFS accepts multiple range/criteria pairs and applies all of them.

For example, with product names in A2:A20 and quantities in B2:B20, to count rows where column A contains “apple” and column B is greater than 10, use =COUNTIFS(A2:A20,"*apple*",B2:B20,">10"). The wildcard works the same way in COUNTIFS criteria.

If you need to count cells that contain any of several substrings (e.g., “apple” OR “orange”), you cannot use a simple OR in COUNTIF. Instead, add two COUNTIF results: =COUNTIF(A2:A20,"*apple*")+COUNTIF(A2:A20,"*orange*"). This counts each cell once per matching condition, so a cell containing both “apple” and “orange” would be counted twice—adjust if your data can have overlaps.

Common Problems and Misconceptions

A frequent mistake is forgetting that COUNTIF treats * and ? as wildcards, so searching for a literal asterisk requires escaping it with a tilde. To count cells containing an actual asterisk, use =COUNTIF(A2:A10,"~*").

Another issue is case sensitivity. COUNTIF is not case-sensitive, so “Apple” and “apple” are treated as the same. If you need case-sensitive partial matching, you must use SUMPRODUCT with ISNUMBER and FIND: =SUMPRODUCT(--ISNUMBER(FIND("apple",A2:A10))). This formula counts cells where “apple” appears exactly with that capitalization.

Also, wildcards do not work when the criteria is a numeric comparison like >5—that’s fine because you’re not using text. But if your substring contains a question mark or asterisk as a literal character, remember to escape them.

Alternatives to COUNTIF for Partial Text

If you need more flexibility—like counting unique partial matches or using dynamic arrays—consider SUMPRODUCT or the newer FILTER function. SUMPRODUCT with ISNUMBER(SEARCH()) is a common alternative because SEARCH is case-insensitive and returns the position of the substring.

For a case-insensitive count, =SUMPRODUCT(--ISNUMBER(SEARCH("apple",A2:A10))) works similarly to COUNTIF but allows you to combine with other conditions more easily. For a case-sensitive version, replace SEARCH with FIND.

In Excel 365, you can also use =ROWS(FILTER(A2:A10,ISNUMBER(SEARCH("apple",A2:A10)))) to get the count of rows where the condition is true. FILTER is available in Excel 2021 and Microsoft 365, but not in older versions.

Frequently Asked Questions

Does COUNTIF work with partial text in Google Sheets? Yes, Google Sheets’ COUNTIF supports the same wildcard syntax. The formulas are identical, so you can use =COUNTIF(A2:A10,"*apple*") in Google Sheets as well. The main difference is that Google Sheets is case-insensitive by default, just like Excel.

How do I count cells that do NOT contain a specific text? Use the not-equal operator with wildcards: =COUNTIF(A2:A10,"<>*apple*"). This counts cells that do not contain “apple”. Note that it also counts empty cells, because an empty cell does not contain “apple”. If you want to exclude blanks, add a condition: =COUNTIFS(A2:A10,"<>*apple*",A2:A10,"<>").

Can I use COUNTIF to count partial matches in a cell that also has numbers? Yes, but the wildcard treats everything as text. If your range contains numbers like 12345 and you search for “*23*”, it will count cells containing the digits 23 anywhere. However, numeric formatting may affect results if cells are stored as numbers with no text—Excel still matches the underlying value as text for wildcards.

Why is my COUNTIF returning 0 when I see the text in the cell? This often happens when the text contains leading or trailing spaces, or when the cell contains a line break. Wildcards match any characters, but spaces are characters too. Try using =COUNTIF(A2:A10,"*"&TRIM(D1)&"*") if the search term has extra spaces, or check for non-breaking spaces (CHAR(160)) that are not matched by a regular space.

Is there a way to make COUNTIF case-sensitive? No, COUNTIF and COUNTIFS are always case-insensitive. You must use SUMPRODUCT with FIND as shown above, or use a helper column with EXACT. For example, =SUMPRODUCT(--EXACT(A2:A10,"apple")) counts exact matches only, but for partial case-sensitive, the FIND method is the standard approach.

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.