How to select dynamic value using COUNTIF Forumula

[yakhtar]

I have a column with multiple values with some separated by a comma. I am using a COUNTIF function to get the count of values - for example, I have a worksheet "Data" from where I am pulling the data as is shown in this formula:
=COUNTIF(Data!D:D,"*Gift cards*")

I am putting the "*" before and after the value to make sure I am including the comma and putting the name of the value (in this case "Gift cards".

Is there a way to pull this data dynamically without having to put the value name? For example: COUNTIF(Data!D:D,"H5") and making sure I am also accounting for values separated by comma?

Column D

e-Gift cards,Gift cards

Gift cards <tbody> </tbody>

Points Transfer <tbody> </tbody>

Travel rewards <tbody> </tbody>

Travel rewards,Gift cards <tbody> </tbody>

<tbody>
</tbody>

 

[kvsrinivasamurthy]

Try this

=COUNTIF(Data!D:D,"*"&"H5"&"*")

 

[yakhtar]

Hi kvsrinivasamurthy, thanks for your response. I am getting a "0" when I use the formula =COUNTIF(Data!D:D,"*"&"H5"&"*"). Any other suggestions?

 

[kvsrinivasamurthy]

=COUNTIF(Data!D:D,"*"&H5&"*")
Try this. Sorry, There was mistake in the formula. Now corrected.

 

[yakhtar]

=COUNTIF(Data!D:D,"*"&H5&"*")
Try this. Sorry, There was mistake in the formula. Now corrected.

Thank you so much! This is exactly what I was looking for.