Count If query within string

[JohnH]

Hi all,

I am trying to count a word within a range of cells. Some cells may contain
the word within a text string for example:
Count the word "red" within "red 1", "red 2", "red 3" etc.
I know this can be done with the formula..."=CountIf(range, "*red*")"

The real question is if this is possible to search by a cell value?
ie. If cell "A1" equals "red" then count "red", if "A1" equals "blue" then
count the number of times "blue" appears.

Hope this makes sense. Thank you.

 

[Bob Phillips]

=COUNTIF(range,"*"&A1&"*")