To count the number of cells that contain a particular text, COUNTIF function can be combined with the Wild Card * (Asterisk).
Table of Contents
Formula to find the Count of cells that contain a specific Text
The following formula will return the number of cells containing the word ‘burger‘ in the range B3:B12.
=COUNTIF(B3:B12,"*burger*")
Explanation
COUNTIF function in Excel will return the count of cells satisfying a specific condition. For example, the following formula will return the count of cells containing the word ‘Apple’.
=COUNTIF(B3:B12,"apple")
I mean, exactly the word Apple. The cells containing the word Apple along with other characters, will be ignored by the formula.
In our case, we want to count the cells contain the word burger and it can be anywhere in the cell. To make COUNTIF function ignore characters other than the word burger, we are using the Wild Card * (Asterisk) before and after the word burger.
In Excel, Asterisk can be used as a placeholder to replace any number of characters. So, when Asterisk is used before and after a word, all characters to the left and right of that word will be ignored by the function.
See the following examples.
=COUNTIF(B5:B15,"*h*") || counts the cells that contain the letter "h" or "H" =COUNTIF(B5:B15,"*5*")|| counts the cells that contain the number "5" =COUNTIF(B5:B15,"*apple*") || counts the cells that contain "Apple", "apple" or "APPLE"
Formula with cell reference
In the above example we were using the direct value ‘burger’. Instead of hard coding the value in the formula, we can use the address of the cell that contains the text to search for.
The following formula returns the number of cells containing the word present in cell D6.
=COUNTIF(B3:B16,"*"&D6&"*")
Here, the cell C6 contains the word ‘Apple‘. So the formula returned the value 7.
Note:
COUNTIF function is not case-sensitive.