Counting Strings Containing Certain Text

Some times we want to calculate how many strings contains certain text. Lets consider this example text:

shahjahankhanalibahadur
shahjahanalibahadurkhan
jahankhanalibahadurshah
shahjahankhanbahadur
jahanalibahadur

I want to find how many of above five contains the text "ali" the formula is simple!! We can use COUNTIF() with a little variation that allows us to "look into each of them for "ali" and then give count of them". Here is the formula:

=COUNTIF(A1:A5,"*"&"ali"&"*")

The result will be 04.

The same thing can be done using SEARCH() with following Array Formula:

=SUM(IFERROR(SEARCH(C1,A1:A5)>=1,0)*1)

This formula seraches for "ALI and gives an array of true & falses and errors, where it find or does not find the match. In order to avoid error message, the IFERROR() function is used to replace them with zeros. When these are multiplied with 1, it assure that all TRUE are replaced by one and then they are summed up by SUM() to give us 04 as a result.

Here is the Sample File