Formula to Count Instances of Locations

[jonisanders]

I have a spreadsheet that has cities on it, I need a formula that will give me the name of the city and how many instances of that city occur on the spreadsheet.
Any ideas? I am using Excel2003

 

[Mel Smith]

If you have a lost of the cities in a column, say Col E, in Col F try the following:
=countif(Range (say $A$1:$A$1000), E1) and then copy down in column F alongside the list in column E.

I hope this helps.

Mel

 

[jonisanders]

Thanks for your assistance but I think I left out a few details.
I have my reports collection on a different worksheet. My city names are in column AB on my data worksheet so my formula on my reports worksheet is =COUNTIF('Phase Report'!AB3:AB183, AB3:AB183). When I do that I get 0 instead of a list of cities and the number of instances that city occurs.
I really appreciate your help.

 

[Mel Smith]

In your =Countif formula you need to specify which city you're counting. In the formula you typed you're trying to count the number of times that the whole range is shown, which does return a '0'. If your range is AB1:AB1000 in your formula you need to give a reference of one city e.g. Cell AC1 etc. Be careful not to refer within the range that you're searching (AB1:AB1000) because you'll create a circular reference.

Mel

 

[jonisanders]

Thanks Mel. I guess that will have to do. I was hoping not to have a to write a formula for each city.

Appreciate your time.

 

[Mel Smith]

Hi,

You don't! If you re-read my first reply, make a list of all possible cities that you will report upon and in the next column to that list put in the =countif formula I quoted (don't forget to make the references absolute bu using the $ symbol) and copy it down.

Good luck.

Mel