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

What would be the scores for constructing Top 5? Number of occurrences? If yes, do we have a pre-set list of countries whose occurrences we can count?

 

[philb99]

Aladin -as before A2 could be holland,spain,iran A3 could be france, usa,canada,syria A4 could be Norway,holland,spain A5 could be spain,germany,finland A6 could be france,holland,sudan and so on
I am trying to establish which countries occurs the most times but limiting it to the op 5

 

[Aladin Akyurek]

Aladin -as before A2 could be holland,spain,iran A3 could be france, usa,canada,syria A4 could be Norway,holland,spain A5 could be spain,germany,finland A6 could be france,holland,sudan and so on
I am trying to establish which countries occurs the most times but limiting it to the op 5

Do we have somewhere a list of possible countries?

 

[philb99]

I have a list of countries in another workbook - would I need to add these as a new tab in this workbook

 

[Aladin Akyurek]

I have a list of countries in another workbook - would I need to add these as a new tab in this workbook

Yes, using tha list we can establish Top 5 countries using their occurrences. Would you then post the names of the sheets along with the relevant ranges?

 

[philb99]

sheet name COR - Range A1:A260

 

[Aladin Akyurek]

sheet name COR - Range A1:A260

Sheet1 houses the data, consisting of strings that contain country names.

DATA

Australia,Cuba,Holland,Sudan,Iran

Spain,Cuba,South Sudan,Canada,Germany

USA,Canada,Sudan,Norway

south sudan,south korea,canada

sudan

nigeria,sudan,Korea,cuba

Australia,Cuba,Holland,France,Iran

Spain,Cuba,SouthSudan,Canada,Germany

USA,Canada,Sudan,Norway

south sudan,south korea

germany,sudan

nigeria,sudan,Korea,turkey

greece,bulgaria

bulgaria,turkey,germany

austria,france,turkey

turkey,italy,spain,greece,bulgaria

USA,Canada,France,Norway

turkey,south korea,usa

sudan

<TBODY>
</TBODY>

Sheet2 contains a country list and the required processing for Top 5...

COUNTRY	COUNT		Top
canada	6		5
england	0		6
france	3		List
germany	4		sudan	8
greece	2		canada	6
holland	2		turkey	5
italy	1		cuba	5
korea	2		germany	4
nigeria	2		usa	4
norway	3
south corea	0
south sudan	3
spain	3
sudan	8
turkey	5
usa	4
bulgaria	3
cuba	5

<TBODY>
</TBODY>

B2, copied down:

=SUMPRODUCT(ISNUMBER(SEARCH(","&$A2&",",","&Sheet1!$A$2:$A$20&","))+0)

D3, just enter:

=COUNTIF(B2:B19,">="&LARGE(B2:B19,D2))

D5, control+shift+enter, not just enter, and copy down:

=IFERROR(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))),"")

E5, just enter and copy down:

=IF(ROWS($E$5:E5)<=D$3,LARGE($B$2:$B$19,ROWS($E$5:E5)),"")

 

[philb99]

Aladin - that's great, although I am struggling with D5 code, the countries are not aligning as per your example- not sure about control shift enter

 

[Aladin Akyurek]

Aladin - that's great, although I am struggling with D5 code, the countries are not aligning as per your example- not sure about control shift enter

Control+shift+enter: Press down the control and the shift keys while you hit the enter key. When done properly, Ezcel puts a pair of { and } appears around the formula.

 

[philb99]

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