Excel COUNTIF Cell Contains Text (Partial Match)

Formulas & functions · Updated November 22, 2026

The direct answer: use =COUNTIF(range,"*text*") to count cells that contain a specific text anywhere within them. For example, =COUNTIF(A2:A10,"*apple*") counts every cell in A2:A10 that has the word “apple” as part of its content, such as “apple pie” or “green apple”.

Why COUNTIF with Wildcards Works

Excel’s COUNTIF function counts cells that meet a condition. The condition can be a number, a cell reference, or a text string. When you want to check if a cell *contains* text rather than equals it exactly, you use wildcard characters. The asterisk (*) represents any sequence of characters (including none), and the question mark (?) represents a single character. Placing an asterisk before and after your search text tells Excel to look for that text anywhere in the cell.

For instance, with data in A2:A5 as “cat”, “catalog”, “scatter”, and “dog”, the formula =COUNTIF(A2:A5,"*cat*") returns 3 because three cells contain the letters “cat” in any position.

Exact vs. Partial Match: The Key Difference

Without wildcards, COUNTIF treats text as an exact match. So =COUNTIF(A2:A5,"cat") would return 1 (only the cell that is exactly “cat”). To count cells that contain a substring, you must add the asterisks. If you want to count cells that start with a specific text, put the asterisk only at the end: =COUNTIF(A2:A5,"cat*") counts “cat” and “catalog” but not “scatter”. Similarly, to count cells ending with text, use =COUNTIF(A2:A5,"*cat").

Counting Cells That Contain Any Text (Non-Blank)

A common need is to count all cells that have *any* text, excluding blanks and numbers. The formula =COUNTIF(A2:A10,"*") counts cells that contain any text string, but it ignores numbers and logical values. If you want to count all non-empty cells (including numbers), use =COUNTA(A2:A10). If you want to count cells that contain at least one character (including spaces), use =SUMPRODUCT(--(LEN(TRIM(A2:A10))>0)) – but for most purposes, =COUNTIF(A2:A10,"*") is sufficient for text-only counting.

Case Sensitivity and Special Characters

COUNTIF is not case-sensitive. =COUNTIF(A2:A5,"*CAT*") will count the same cells as =COUNTIF(A2:A5,"*cat*"). If you need a case-sensitive count, you must use SUMPRODUCT with the FIND function: =SUMPRODUCT(--ISNUMBER(FIND("cat",A2:A5))). Note that FIND is case-sensitive, while SEARCH is not.

Wildcards also have a special role. If you actually want to count cells that contain an asterisk or question mark, you need to escape them with a tilde (~). For example, =COUNTIF(A2:A5,"*~**") counts cells that contain an asterisk anywhere. The first and last asterisks are wildcards, and the ~* tells Excel to treat the middle asterisk as a literal character.

Using Cell References in the Criteria

Often you want the search text to come from another cell. You can concatenate the wildcards with a cell reference. Assuming B1 contains the text you’re looking for, use =COUNTIF(A2:A10,"*"&B1&"*"). This works because the ampersand (&) joins the asterisk, the cell value, and the second asterisk into a single text string. If B1 is empty, the formula counts all cells containing any text (because it becomes “**”).

Common Problems and How to Avoid Them

  • Counting numbers that look like text: If your cells contain numbers stored as text (e.g., “123”), =COUNTIF(A2:A10,"*123*") will count them, but =COUNTIF(A2:A10,"123") might not because of formatting. To be safe, use the wildcard version.
  • Trailing spaces: If cells have extra spaces, the wildcard might not match as expected. Use TRIM in a helper column or use =SUMPRODUCT(--ISNUMBER(SEARCH("text",TRIM(A2:A10)))).
  • Counting cells that contain one of several texts: You can’t do that directly with a single COUNTIF. Use =COUNTIF(A2:A10,"*apple*")+COUNTIF(A2:A10,"*orange*"), but be aware that cells containing both will be counted twice. To avoid double counting, use =SUMPRODUCT(--((ISNUMBER(SEARCH("apple",A2:A10)))+(ISNUMBER(SEARCH("orange",A2:A10)))>0)).

Alternatives: COUNTIFS, SUMPRODUCT, and Dynamic Arrays

For multiple conditions, use COUNTIFS. For example, to count cells in A2:A10 that contain “apple” and have a value in B2:B10 greater than 5, use =COUNTIFS(A2:A10,"*apple*",B2:B10,">5"). The wildcard works the same way.

SUMPRODUCT offers more flexibility, especially for case-sensitive checks or when you need to test multiple criteria without double counting. For dynamic arrays (Excel 365), you can use =SUM(--ISNUMBER(SEARCH("text",A2:A10))), which spills a single result. However, COUNTIF remains the simplest and fastest for basic contains-text counting.

Frequently Asked Questions

How do I count cells that contain text but not numbers?

Use =COUNTIF(A2:A10,"*"). This counts cells that contain any text string, ignoring numbers, dates, and logical values. If you want to include numbers as well, use =COUNTA(A2:A10).

Can I use COUNTIF with a partial match and a cell reference?

Yes, concatenate the wildcards with the cell reference: =COUNTIF(A2:A10,"*"&B1&"*"). If B1 contains “apple”, it counts cells containing “apple” anywhere. If B1 contains a wildcard character itself, it will be treated as a wildcard unless you escape it with a tilde.

Why does my COUNTIF return 0 when I know there is text?

Check for extra spaces, non-printing characters, or that the cells aren’t actually numbers formatted as text. Use =TRIM(A2) to see the cleaned value. Also, if you’re using a wildcard like “*text*”, ensure the text doesn’t contain characters that Excel interprets as wildcards (like * or ?) – escape them with ~.

Is COUNTIF case-sensitive?

No, COUNTIF is not case-sensitive. For case-sensitive counting, use =SUMPRODUCT(--ISNUMBER(FIND("text",A2:A10))). FIND is case-sensitive, while SEARCH is not.

How do I count cells that contain text from a list of words?

You can use multiple COUNTIFs added together, but that double-counts cells with multiple matches. A better approach is =SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(TRANSPOSE("apple";"orange";"banana"),A2:A10)),ROW(INDIRECT("1:3"))^0)>0)) – but this is complex. Simpler: use a helper column with a formula like =IF(SUMPRODUCT(--ISNUMBER(SEARCH({"apple";"orange";"banana"},A2)))>0,1,0) and sum that column.

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.