CountIF cells that contain a specific criteria- Wildcard Countif (Excel Training)

CountIF cells that contain a specific criteria- Wildcard Countif

However, if you ever need to count the number of cells in a range of cells that include a specific word or string of characters (not necessarily the entire cell), the trick is to surround your criteria with asterisks (*).

The asterisk is a wildcard that represents any single character or string of characters.

Whereas, =COUNTIF(B:B,”dog”) will count the number of cells in column B that have the word ‘dog’, by adding asterisks to the beginning and end of your criteria, for example, =COUNTIF(B:B,”*dog*”) will count all cells that ‘include’ the word ‘dog’ anywhere within the cell, such as ‘dog jackets’ or ‘reversible dog coats’.

You can make this function even more flexible by using a cell reference for your criteria. In that case, you can’t simply type asterisks before and after your criteria cell reference. To add asterisks before and after the cell reference, you will need to type the asterisk surrounded by double quotes and use ampersands (&) to concatenate them with the cell reference. For example, if cell D1 contains the word ‘dog’, you can rewrite your formula to be =COUNTIF(B:B,”*” & D1 & “*”) which is equivalent to =COUNTIF(B:B,”*dog*”).

Now you can simply change the value in cell D1 to count cells based on new criteria or, better yet, use multiple cells for your criteria and copy your COUNTIF formula to reference these cells. If cell D1 contains ‘dog’ and cell D2 contains ‘cat’ you can enter =COUNTIF(B:B,”*” & D1 & “*”) in cell E1 and copy it down to cell E2 to give you counts for both your criterion

Count the number of occurrences of a word or text string in a range with the COUNTIF function