Google Sheets – How to Include Text from a Cell within a COUNTIF Function

formulasgoogle sheets

I am using sheets to mark quiz responses. I am trying to display a 1 if the answer contains a certain string (therefore, is correct).

For example, COUNTIF(d2, "*serbia").
So, if D2 contains this text, display a 1.

However, I want to get the text "serbia" (for example) from another cell. So a separate cell just contains "serbia" and I refer to that cell in the COUNTIF function.

So, if D2 contains the text from C19, display a 1.

Best Answer

You can use & to create the criterion you need. Since the given answer may not end with the correct answer, I would add another * as well:

=COUNTIF(D2,"*"&C19&"*")

Demo

enter image description here

Related Topic