Count Values in Cells

[philb99]

I have a list of countries recorded in Cells from A2 to A1000, some cells have 4 or more countries recorded, and I want to Count certain countries

I have been using this Formula where I have the country that I am looking for entered into Cell C1

=COUNTIF($A$2:$A$100,"*"&C$1&"*")

What it doesn't do is separate those countries with some of the same name like Sudan and South Sudan also North Korea and South Korea

Therefore can any on help with the formula

 

[Momentman]

Good enough Mr Aladin has bailed me/us out

In any case, whats wrong with the formula?, could you post what your expected results are and maybe what the sample data looks like

 

[philb99]

Thanks Aladin - reading your caveat I do not have a space after the comma and therefore this is the reason it does not work - any idea how we work it

 

[philb99]

Thanks Momentman - the adding of Sudan = 5 in your table, this has added both Sudan and South Sudan, I require those only for Sudan which would = 4

Between my countries I only have a comma - no spaces For example Iran,Holland,Spain,Sudan.

All of my countries are recorded in A1 to say A1000 - each Cell could have 4 or more countries recorded

 

[Aladin Akyurek]

Thanks Aladin - reading your caveat I do not have a space after the comma and therefore this is the reason it does not work - any idea how we work it

Try...

=SUMPRODUCT(ISNUMBER(SEARCH(","&C$1&",",","&$A$2:$A$100&","))+0)

 

[philb99]

Thanks Aladin - that's done the trick

Really appreciate you help

 

[shahfahad]

Hey Aladin. What does the "#" symbol does?

 

[Aladin Akyurek]

Thanks Aladin - that's done the trick

Really appreciate you help

You are welcome.

 

[Aladin Akyurek]

Hey Aladin. What does the "#" symbol does?

Used in:

=SUMPRODUCT(ISNUMBER(SEARCH("#"&C$1&"#","#"&SUBSTITUTE($A$2:$A$100,", ","#")&"#"))+0)

in order to count:

C1 = Sudan

in data like:

Australia, Cuba, Holland, Sudan, Iran

Spain, Cuba, South Sudan, Canada, Germany

<TBODY>
</TBODY>

The issue is to distinguish between Sudan and South Sudan.

Note that the items are separated with a comma followed by a space (The original poster does not have space.) The formula establishes "#" as separator instead of ", ", using SUBSTITUTE. So, there isn't anything special to the "#" char. A "|" for example would be equally convenient. Try using the Evaluate Formula tool how the substitution is done.

 

[shahfahad]

Thank you so much. You are probably the Grand Daddy of formula making. I'm always learning from you.

 

[philb99]

Aladin - one further question if I may

We were able to Count specific countries but is there a formula I could use to establish what the top 5 countries would be from all of those recorded in Column A2 to A15000