Excel COUNTIF Contains Text from Another Cell?

Formulas & functions · Updated November 22, 2026

Yes, use COUNTIF(range, "*"&cell&"*") to count cells that contain the text from another cell. For example, =COUNTIF(A2:A10, "*"&B1&"*") counts how many cells in A2:A10 contain the text in B1 anywhere within them.

How COUNTIF Handles Text Matching

Excel’s COUNTIF function counts cells that meet a single condition. When the condition is text, it matches exact text by default. To match text that appears anywhere inside a cell, you wrap the search text with asterisks (*), which are wildcards that represent any number of characters. The formula "*"&B1&"*" tells Excel to find cells that start with anything, contain the value of B1, and end with anything.

This works for partial matches, so a cell containing “apple pie” will be counted if the reference cell contains “apple”. The match is case-insensitive, so “Apple” and “apple” are treated the same.

Practical Example with a Worked Formula

Suppose you have a list of product names in A2:A10 and you want to count how many contain the word “pro” typed in cell B1.

  • In B1, enter: pro
  • In C1, enter: =COUNTIF(A2:A10, "*"&B1&"*")

If A2:A10 contains “ProDesk”, “Laptop Pro”, “ProBook”, “Tablet”, and “ProMax”, the formula returns 4 because four cells contain “pro” (case-insensitive). The asterisks are essential; without them, =COUNTIF(A2:A10, B1) would only count exact matches like “pro” alone.

Using Cell References in Criteria

You can reference any cell in the criteria string. The ampersand (&) concatenates the asterisks with the cell value. For example, if the search text is in D5, the formula is =COUNTIF(A2:A10, "*"&D5&"*"). This works in Excel 2010 and later, including Excel for Microsoft 365, Excel 2021, and Excel for the web. On Mac, the same formula works, but you may need to use semicolons as argument separators if your regional settings use them; the standard comma works in most English versions.

Common Problems and Solutions

  • No matches returned: Check that the reference cell has no extra spaces. Use =COUNTIF(A2:A10, "*"&TRIM(B1)&"*") to remove leading/trailing spaces.
  • Counting cells that contain a number: If B1 contains a number, the asterisks still work because Excel converts the number to text for the criteria. For instance, =COUNTIF(A2:A10, "*"&123&"*") counts cells containing “123” as text.
  • Wildcard characters in the reference: If B1 contains an asterisk or question mark, Excel treats them as wildcards, not literal characters. To count a literal asterisk, use a tilde (~) before it, like "*"&SUBSTITUTE(B1,"*","~*")&"*".
  • Case sensitivity: COUNTIF is not case-sensitive. If you need case-sensitive counting, use SUMPRODUCT with ISNUMBER and FIND, but that is more complex.

Alternatives: COUNTIFS, SUMPRODUCT, and Dynamic Arrays

For multiple conditions, use COUNTIFS with the same wildcard technique. For example, to count cells in A2:A10 that contain B1 and are also marked “Yes” in C2:C10, use =COUNTIFS(A2:A10, "*"&B1&"*", C2:C10, "Yes").

If you need to count based on a range of search terms, SUMPRODUCT can sum counts: =SUMPRODUCT(--ISNUMBER(SEARCH(B1, A2:A10))). This returns the same count but is case-insensitive and can be extended to multiple terms. In Excel 365, you can also use =SUM(--(ISNUMBER(SEARCH(B1, A2:A10)))) as a dynamic array formula that spills automatically.

Counting Cells That Contain Any of Several Text Strings

To count cells that contain text from any cell in a list, you can combine COUNTIF with SUMPRODUCT. Suppose search terms are in D2:D4. Use =SUMPRODUCT(--(ISNUMBER(SEARCH(D2:D4, A2:A10)))) but note that this counts each occurrence, not unique cells. For unique cells, use =SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(D2:D4), A2:A10)), ROW(INDIRECT("1:"&ROWS(D2:D4)))^0)>0)). This is advanced and requires Ctrl+Shift+Enter in older versions.

Frequently Asked Questions

How do I count cells that contain a specific word, not just part of a word?

Use a formula with spaces around the word: =COUNTIF(A2:A10, "* "&B1&" *") plus handle the first and last words separately. A simpler approach is =SUMPRODUCT(--(ISNUMBER(SEARCH(" "&B1&" ", " "&A2:A10&" ")))).

Can COUNTIF reference a cell with a formula result?

Yes, the cell can contain any formula that returns text. The reference cell’s value is used as-is, so =COUNTIF(A2:A10, "*"&B1&"*") works even if B1 contains =CONCATENATE("pro","duct").

Why does my COUNTIF return 0 when the text is clearly there?

Check for non-printing characters like line breaks or extra spaces. Use =COUNTIF(A2:A10, "*"&TRIM(CLEAN(B1))&"*") to clean the reference. Also ensure the range includes the correct cells and that the text isn’t a number formatted as text.

Is there a way to count cells that do NOT contain a certain text?

Use the not-equal operator with wildcards: =COUNTIF(A2:A10, "<>*"&B1&"*"). This counts cells that do not contain the text in B1 anywhere.

Does COUNTIF work with text from another sheet?

Yes, reference the other sheet normally, like =COUNTIF(A2:A10, "*"&Sheet2!B1&"*"). The sheet name with spaces needs single quotes: ='My Sheet'!B1.

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.