COUNTIF - Problems specify the criteria as a range

[palmbum]

Hi I have a problem trying to apply the COUNTIF function.

Let me explain my difficulties

I have the following cells with data formatted like this:

A
Countries Selected
Cambodia Thailand Philippines Laos Vietnam Belgium [A2]
Vietnam Philippines Thailand Cambodia Laos [A3]
Vietnam Cambodia Thailand Philippines Laos [A4]
Cambodia Thailand Philippines Laos Vietnam [A5]
Germany Thailand Cambodia Vietnam Laos Philippines [A6]

B
Countries
Laos
Cambodia
Thailand

C
Number of Instances


So basically, I need to count how many times a certain text string, corresponding to column B, (e.g. "Laos" ) occurs in ranges A2-6, and then output the result in column C.

If I formulate the function in C2 as COUNTIF(A2:A6, "*Laos*), it gives me the correct value, but I want my criteria to reference the countries listed in column B.

But my formula of COUNTIF(A2:A6, "*B2*) keeps giving me an error.

Please help!

[TMS]

Try:

=COUNTIF(A$2:A$6, "*"&B2&"*")


Regards, TMS

[palmbum]

Try:

=COUNTIF(A$2:A$6, "*"&B2&"*")


Regards, TMS

OMG... it worked! Gosh... this problem has been plaguing me for 3 days! Thank you so much!

[TMS]

You're welcome.