Excel COUNTIF Contains Partial Text: Formula Guide

Formulas & functions · Updated November 22, 2026

The formula to count cells containing partial text in Excel is =COUNTIF(range,"*text*"). Replace range with your cell range and text with the partial string you want to match, using asterisks as wildcards. For example, =COUNTIF(A2:A20,"*apple*") counts every cell in A2:A20 that contains the word “apple” anywhere within it.

How COUNTIF Wildcards Work

Excel’s COUNTIF function accepts three wildcard characters that let you match partial text without writing complex formulas. The asterisk (*) represents any number of characters (including zero), the question mark (?) represents exactly one character, and the tilde (~) escapes the other two when you need to search for a literal asterisk or question mark. When you place an asterisk before and after your search term, as in "*text*", you tell Excel to find cells where that text appears at the start, middle, or end. If you only want matches at the beginning, use "text*"; for matches at the end, use "*text". The wildcard approach works on text, numbers stored as text, and even mixed content, but it does not work on pure numeric values unless those numbers are formatted as text.

Basic Example with a Real Dataset

Assume column A contains product names in cells A2:A10: “Apple Pie”, “Banana Bread”, “Apple Juice”, “Cherry Tart”, “Green Apple”, “Pineapple”, “Apple Sauce”, “Grape”, “Apple Cider”. To count how many cells contain the word “Apple” (case-insensitive), use =COUNTIF(A2:A10,"*Apple*"). Excel counts “Apple Pie”, “Apple Juice”, “Green Apple”, “Apple Sauce”, and “Apple Cider” — that is 5 matches. Note that “Pineapple” is not counted because the string “Apple” appears inside “Pineapple” but the wildcard pattern *Apple* actually would match it, since it looks for the exact sequence “Apple” anywhere. In this dataset, “Pineapple” contains “Apple” as a substring, so it would be counted too, giving 6. To avoid matching substrings inside other words, you would need a more precise pattern like "* Apple *" with spaces, but that fails at the start or end of a cell. For most partial-text counting, the simple *text* is exactly what you want.

Using Cell References for the Search Term

Hardcoding text in the formula works, but you often want to reference a cell that contains the search term. To do this, concatenate the wildcards with the cell reference using the ampersand (&). With the search term in cell D1, the formula becomes =COUNTIF(A2:A10,"*"&D1&"*"). If D1 contains “Apple”, the formula counts the same 6 cells as above. This approach makes it easy to change the search term without editing the formula. For a case-sensitive count, you cannot use COUNTIF directly; instead, use =SUMPRODUCT(--ISNUMBER(SEARCH("Apple",A2:A10))) for case-insensitive, or =SUMPRODUCT(--ISNUMBER(FIND("Apple",A2:A10))) for case-sensitive. SEARCH and FIND return the position of the text if found, and ISNUMBER converts that to TRUE/FALSE, then the double unary (--) turns TRUE/FALSE into 1/0 for SUMPRODUCT to sum.

Counting with Multiple Partial Text Criteria

Sometimes you need to count cells that contain any of several partial strings, or all of them. For counting cells that contain either “Apple” OR “Banana”, use the COUNTIFS function with wildcards and add the results: =COUNTIF(A2:A10,"*Apple*")+COUNTIF(A2:A10,"*Banana*"). This counts cells that match either pattern, but if a cell contains both “Apple” and “Banana”, it gets counted twice. To count distinct cells that contain at least one of the terms, use =SUMPRODUCT(--((ISNUMBER(SEARCH("Apple",A2:A10)))+(ISNUMBER(SEARCH("Banana",A2:A10)))>0)). For cells that contain both terms (AND logic), use =SUMPRODUCT(--(ISNUMBER(SEARCH("Apple",A2:A10))*ISNUMBER(SEARCH("Banana",A2:A10)))). These array-like formulas work without pressing Ctrl+Shift+Enter in modern Excel versions, but they require that your range does not contain entire-column references like A:A, because that would slow down calculation significantly.

Common Problems and How to Fix Them

  • Counting blank cells or cells with only spaces: The pattern "*" counts all non-empty cells, including those with just a space. To exclude spaces, use =COUNTIF(range,"?*") which requires at least one character.
  • Case sensitivity: COUNTIF is not case-sensitive. If you need case-sensitive partial matching, use the SUMPRODUCT with FIND as shown above.
  • Wildcards not working on numbers: If your cells contain numeric values like 12345 and you use "*23*", it will not count them because COUNTIF treats numbers as numbers, not text. Convert numbers to text first using TEXT function in a helper column, or use SUMPRODUCT with SEARCH on the text conversion.
  • Searching for literal asterisk or question mark: Use a tilde before the character, e.g., =COUNTIF(A2:A10,"*~?*") to count cells containing a question mark.
  • Entire column references: Using A:A works but can be slow with large data. Limit the range to your actual data extent, like A2:A1000.

Alternatives to COUNTIF for Partial Text

If you are using Excel 365 or Excel 2021, you have access to newer functions that offer more flexibility. The FILTER function combined with COUNTA can count partial matches: =COUNTA(FILTER(A2:A10,ISNUMBER(SEARCH("Apple",A2:A10)))). This returns the count of rows where SEARCH finds “Apple”. Another option is the SUMPRODUCT with ISNUMBER and SEARCH, which works in all Excel versions. For dynamic arrays, you could use =ROWS(FILTER(A2:A10,ISNUMBER(SEARCH("Apple",A2:A10)))) but COUNTA is simpler. If you need to count unique partial matches, combine UNIQUE with FILTER: =COUNTA(UNIQUE(FILTER(A2:A10,ISNUMBER(SEARCH("Apple",A2:A10))))). These alternatives are more verbose but allow case-sensitive matching via FIND, and they can be extended to multiple criteria more easily than COUNTIFS.

Performance and Large Datasets

COUNTIF with wildcards is generally fast, but when applied to entire columns (e.g., A:A) on a sheet with hundreds of thousands of rows, it can slow down workbook recalculations. For large datasets, limit the range to the actual used rows. If you need to count partial text across multiple columns, use SUMPRODUCT with SEARCH and a range that covers all columns, but be aware that SUMPRODUCT is an array calculation and can be slower than COUNTIF. A practical approach is to add a helper column that uses =IF(ISNUMBER(SEARCH("Apple",A2)),1,0) and then sum that column. This is often faster for very large data because it calculates once per row rather than scanning the entire range repeatedly. Another tip: if your data is sorted, you could use approximate match with MATCH, but that is overkill for most cases.

Frequently Asked Questions

Can COUNTIF count partial text in a cell that has numbers and letters mixed? Yes, as long as the cell is formatted as text or contains a string. If the cell contains a numeric value like 123, wildcards will not match because Excel treats it as a number. You can convert it to text using TEXT(A2,”0″) in a helper column.

Why does my COUNTIF return 0 when I know the text is there? Check for leading or trailing spaces in the data or in your search term. Also verify that the range is correct and that you are using straight quotes, not curly quotes. If the text is in a merged cell, COUNTIF only sees the top-left cell.

How do I count cells that do NOT contain partial text? Use =COUNTIF(range,"*text*") to count matches, then subtract from the total non-blank cells: =COUNTA(range)-COUNTIF(range,"*text*"). Or use =COUNTIF(range,"<>*text*") which counts cells that do not match the pattern, but this also counts blanks, so combine with =COUNTIFS(range,"<>*text*",range,"?*") to exclude blanks.

Is COUNTIF case-sensitive? No, COUNTIF is not case-sensitive. For case-sensitive partial text counting, use =SUMPRODUCT(--ISNUMBER(FIND("text",range))). FIND is case-sensitive, whereas SEARCH is not.

Can I use COUNTIF to count partial text in another sheet? Yes, reference the sheet name in the range, like =COUNTIF(Sheet2!A2:A100,"*text*"). If the sheet name contains spaces, use single quotes: =COUNTIF('My Sheet'!A2:A100,"*text*").

What is the difference between COUNTIF and COUNTIFS for partial text? COUNTIF handles a single condition, while COUNTIFS can handle multiple conditions across different ranges. For partial text, you can use COUNTIFS with wildcards on multiple columns, e.g., =COUNTIFS(A2:A10,"*Apple*",B2:B10,"*Red*") counts rows where column A contains “Apple” and column B contains “Red”.

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.