COUNTIF Contains Text: Count Partial Matches

COUNTIF contains text counts the cells where a word appears anywhere, not only cells that match it exactly. The trick is the asterisk wildcard: =COUNTIF(A2:A8,"*blue*") counts every cell with "blue" somewhere inside. This guide shows contains, starts with and ends with, how to use a word from a cell, how to count cells that don't contain a word, and what to do when you need capitals to matter.

Why an exact COUNTIF misses partial matches#

A plain text criteria in COUNTIF must match the whole cell. Look at this product list in A1:A8:

=COUNTIF(A2:A8,"blue") returns 0. No cell is exactly "blue", so nothing matches, even though four products clearly contain the word. To match part of a cell, you need wildcards. The basics of COUNTIF are in how to use COUNTIF.

How to count cells that contain text#

Put an asterisk (*) on each side of the word. In a criteria, * stands for "any number of characters, including none":

=COUNTIF(A2:A8,"*blue*")

The result is 4: Blue T-shirt, Navy Blue Jacket, Blue Jeans and Light blue Cap. Read the pattern as "anything, then blue, then anything". COUNTIF ignores capitals, so "Light blue Cap" counts even though it uses a lower-case b.

The word can be part of a longer word, too. "*shirt*" matches "T-shirt" and would also match "Sweatshirt". If you only want whole words, add spaces inside the pattern, such as "* shirt*", and check the results by eye.

Starts with and ends with#

Move the asterisk to choose where the word must appear:

"Starts with blue" finds Blue T-shirt and Blue Jeans. "Ends with hoodie" finds Red Hoodie and Black Hoodie. The <> in front means "not", so "<>*blue*" counts the three products without blue: Red Hoodie, Green T-shirt and Black Hoodie. Be aware that it also counts blank cells, because an empty cell doesn't contain blue either.

There is a second wildcard, ?, which stands for exactly one character. "T-shirt?" would match "T-shirts" but not "T-shirt". The guide to wildcards in COUNTIF and SUMIF covers both in depth.

How to use a word from a cell#

To change the word without editing the formula, type it in a cell and join the asterisks to it with &. With hoodie in E1:

=COUNTIF(A2:A8,"*"&E1&"*")

The result is 2. Excel builds the text "*hoodie*" and then counts. Change E1 to blue and the result becomes 4. The asterisks must be inside quotes, and the cell reference outside them. Writing "*E1*" searches for the letters E1.

This setup is useful for a small summary table: list several keywords in E1:E3, put the formula next to the first one with the range locked ($A$2:$A$8), and fill it down.

How to count cells that contain two words#

For AND logic, where a cell must contain both words, use COUNTIFS with the same range twice:

=COUNTIFS(A2:A8,"*blue*",A2:A8,"*shirt*")

The result is 1: only Blue T-shirt contains both. For OR logic, where either word counts, adding two COUNTIFs works only when no cell contains both. =COUNTIF(A2:A8,"*blue*")+COUNTIF(A2:A8,"*hoodie*") gives 4 + 2 = 6 here, which is right because no product is a blue hoodie. If one were, it would be counted twice.

Case-sensitive contains with FIND#

COUNTIF never cares about capitals. When case matters, for example to count product codes that contain "XL" but not "xl", use SUMPRODUCT with FIND:

=SUMPRODUCT(--ISNUMBER(FIND("Blue",A2:A8)))

FIND returns the position of "Blue" in each cell, or an error if it isn't there, and it respects capitals. ISNUMBER turns each result into TRUE or FALSE, and -- turns those into 1s and 0s for SUMPRODUCT to add. The result is 3: Light blue Cap is left out because its "blue" is lower case.

Why COUNTIF contains text returns 0#

If the count is lower than you expect, check these:

1. No asterisks. "blue" only matches cells that are exactly "blue". Use "*blue*".
2. The cell reference is inside the quotes. Write "*"&E1&"*", not "*E1*".
3. The values are numbers. Wildcards only match text. "*5*" won't count the number 150, because 150 is a number, not text.
4. Spelling differences. "Grey" and "Gray" are different words to Excel. Hidden extra spaces don't break a contains match, but they do break exact matches.
5. You need a real asterisk. To count cells that contain an actual * or ?, put a tilde in front: "*~**".

For more causes, see COUNTIF not working. To count any text cell at all, read how to count cells with text, and to add up numbers for the matching rows instead of counting them, use SUMIF contains text.

How to list the matching cells, not just count them#

A count tells you how many cells match, but sometimes you need to see which ones. In Excel 2021 or Microsoft 365, FILTER with SEARCH lists them:

=FILTER(A2:A8,ISNUMBER(SEARCH("blue",A2:A8)))

The formula spills the four blue products into the cells below it. SEARCH ignores capitals, like COUNTIF, and it doesn't need asterisks, because it already looks for the text anywhere in the cell. In older versions, add a helper column with =ISNUMBER(SEARCH("blue",A2)), fill it down, and filter it for TRUE (Data > Filter). Comparing the list with your COUNTIF result is a quick way to check that the pattern matches what you meant.

Practise it#

Wildcards look strange until you have typed them a few times. Practise wildcards in criteria in a live spreadsheet, then practise COUNTIF with every kind of criteria. Each lesson checks your formula instantly. The COUNTIF function guide has the syntax on one page.