google sheets COUNTIF range contains a string (defined using cell referencing)

Question

I have text defined in column A, and in column B I want to search another sheet for cells containing these strings. I have tried the COUNTIF function, which can be used to count cells containing the exact text, but not for cells which include the defined string:

A         B        
red
green
blue

If in column B I use =COUNTIF(sheet2!F1:F10,A1), I can count cells which contain 'red' exactly, but not cells which may include 'red' amongst other text.

I know you can use =COUNTIF(sheet2!F1:F10,"\*red\*") when the string is already defined, is there a way to to this when the string is referenced from another cell?

Answer 1

Yes, concatenate the string with the variable:

=COUNTIF(sheet2!F1:F10,"*" & A1 & "*")