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?


[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]e-Gift cards,Gift cards[/TD]
[/TR]
[TR]
[TD][TABLE="width: 520"]
<tbody>[TR]
[TD="width: 520"]Gift cards[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 520"]
<tbody>[TR]
[TD="width: 520"]Points Transfer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 520"]
<tbody>[TR]
[TD="width: 520"]Travel rewards[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 520"]
<tbody>[TR]
[TD="width: 520"]Travel rewards,Gift cards[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.