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

 

[Aladin Akyurek]

Aladin - I can now work these 4 formulas in Excel 2007 but when I try in 2003 version one of the formula does not work (its the D5 above), a warning states that NAME will be displayed. All of the remaining 3 formulas work

Anything you can do for me please

You get a #NAME? error because 2003 does not have the IFERROR function.

Replace the formula in D5 with the one that follows...

=IF($E5="","",INDEX($A$2:$A$19,SMALL(IF($B$2:$B$19=$E5,
  ROW($B$2:$B$19)-ROW($B$2)+1),COUNTIF($E$5:E5,E5))))

which must be confirmed with control+shift+enter.

 

[philb99]

Top man Aladin - that's done it

 

[Aladin Akyurek]

Top man Aladin - that's done it

You are welcome.