Count names and occurrences

M

Midjack

I have a long list of cities (Col. A) and states (Col. B). I would like to
count the number of times each city occurs without having to input the city
name each time; with the results appearing like this:

Col. A Col. B Col. C
City State Number of occurrences

Boston Massachusetts 300
Denver Colorado 250
New Orleans Louisiana 256
etc.

Anyone know how to do this?

Thanks
 
M

Midjack

I tried that and got a list of unique cities, but when I ran =COUNTIF(A:A,G2)
all I got were 0's.
 
M

Midjack

Here are the first 5 or 6 hundred after they have been sorted by state and
city. Thanks for bothering with this.

United States Birmingham Alabama 3
United States Birmingham Alabama 3
United States Birmingham Alabama 3
United States Decatur Alabama 2
United States Dothan Alabama 1
United States Florence Alabama 2
United States Huntsville Alabama 3
United States Huntsville Alabama 3
United States Huntsville Alabama 3
United States Madison Alabama 13
United States Madison Alabama 13
United States Mobile Alabama 4
United States Mobile Alabama 4
United States Mobile Alabama 4
United States Mobile Alabama 4
United States Montgomery Alabama 3
United States Montgomery Alabama 3
United States Montgomery Alabama 3
United States Selma Alabama 1
United States Talladega Alabama 1
United States Trinity Alabama 1
United States Anchorage Alaska 1
United States Chandler Arizona 1
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Glendale Arizona 18
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Phoenix Arizona 11
United States Prescott Arizona 2
United States Snowflake Arizona 1
United States Tempe Arizona 1
United States Tucson Arizona 3
United States Tucson Arizona 3
United States Tucson Arizona 3
United States Arkadelphia Arkansas 1
United States Bentonville Arkansas 2
United States Bentonville Arkansas 2
United States Camden Arkansas 1
United States Caraway Arkansas 1
United States Clinton Arkansas 1
United States Conway Arkansas 2
United States Conway Arkansas 2
United States El Dorado Arkansas 1
United States Fayetteville Arkansas 5
United States Fordyce Arkansas 1
United States Harrison Arkansas 5
United States Harrison Arkansas 5
United States Harrison Arkansas 5
United States Harrison Arkansas 5
United States Harrison Arkansas 5
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Little Rock Arkansas 20
United States Maumelle Arkansas 1
United States Mount Holly Arkansas 2
United States Mount Holly Arkansas 2
United States Newhope Arkansas 1
United States North Little Rock Arkansas 1
United States Prescott Arkansas 2
United States Rogers Arkansas 1
United States Rose Bud Arkansas 1
United States Searcy Arkansas 1
United States Springdale Arkansas 1
United States Strong Arkansas 1
United States Subiaco Arkansas 3
United States Subiaco Arkansas 3
United States Subiaco Arkansas 3
United States Alameda California 1
United States Burbank California 1
United States Calpella California 1
United States Citrus Heights California 1
United States Corona California 1
United States Davis California 1
United States Dublin California 26
United States Edwards California 10
United States El Sobrante California 1
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Emeryville California 27
United States Fairfield California 4
United States Frazier Park California 1
United States Fresno California 1
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States Goleta California 14
United States La Habra California 1
United States La Puente California 1
United States Livermore California 1
United States Long Beach California 1
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Los Angeles California 14
United States Modesto California 1
United States Montebello California 1
United States Moreno Valley California 1
United States Mountain View California 2
United States Mountain View California 2
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Oakland California 11
United States Occidental California 1
United States Oceanside California 9
United States Oceanside California 9
United States Oceanside California 9
United States Oceanside California 9
United States Oceanside California 9
United States Oceanside California 9
United States Oceanside California 9
United States Oceanside California 9
United States Oceanside California 9
United States Palo Alto California 3
United States Palo Alto California 3
United States Palo Alto California 3
United States Petaluma California 3
United States Petaluma California 3
United States Petaluma California 3
United States Posey California 1
United States Poway California 1
United States Quincy California 1
United States Ramona California 1
United States Sacramento California 4
United States Sacramento California 4
United States Sacramento California 4
United States Sacramento California 4
United States San Clemente California 1
United States San Diego California 3
United States San Diego California 3
United States San Diego California 3
United States San Francisco California 3
United States San Francisco California 3
United States San Francisco California 3
United States San Jose California 5
United States San Jose California 5
United States San Jose California 5
United States San Jose California 5
United States San Jose California 5
United States San Leandro California 1
United States Santa Ana California 1
United States Santa Monica California 1
United States Scotts Valley California 1
United States Sherman Oaks California 1
United States Simi Valley California 1
United States Stanton California 1
United States Tehachapi California 1
United States Vina California 1
United States Vista California 9
United States Vista California 9
United States Vista California 9
United States Vista California 9
United States Vista California 9
United States Vista California 9
United States Vista California 9
United States Vista California 9
United States Vista California 9
United States Walnut Creek California 2
United States Walnut Creek California 2
United States West Covina California 1
United States Westminster California 1
United States Whittier California 1
United States Windsor California 3
United States Yorba Linda California 1
United States Broomfield Colorado 1
United States Colorado Springs Colorado 2
United States Colorado Springs Colorado 2
United States Denver Colorado 7
United States Denver Colorado 7
United States Denver Colorado 7
United States Denver Colorado 7
United States Denver Colorado 7
United States Englewood Colorado 1
United States Fort Collins Colorado 5
United States Fort Collins Colorado 5
United States Fort Collins Colorado 5
United States Fort Collins Colorado 5
United States Fort Collins Colorado 5
United States Golden Colorado 1
United States Greeley Colorado 1
United States Littleton Colorado 2
United States Littleton Colorado 2
United States Pagosa Springs Colorado 2
United States Pagosa Springs Colorado 2
United States Fairfield Connecticut 4
United States Hartford Connecticut 3
United States Hartford Connecticut 3
United States Hartford Connecticut 3
United States Meriden Connecticut 1
United States Monroe Connecticut 86
United States Stamford Connecticut 2
United States Stamford Connecticut 2
United States Vernon Rockville Connecticut 1
United States Wallingford Connecticut 2
United States Wallingford Connecticut 2
United States Bear Delaware 1
United States Wilmington Delaware 3
United States Washington District of Columbia 8
United States Washington District of Columbia 8
United States Washington District of Columbia 8
United States Washington District of Columbia 8
United States Washington District of Columbia 8
United States Washington District of Columbia 8
United States Washington District of Columbia 8
United States Washington District of Columbia 8
United States Boca Raton Florida 1
United States Crystal River Florida 4
United States Crystal River Florida 4
United States Crystal River Florida 4
United States Crystal River Florida 4
United States Eglin Afb Florida 3
United States Eglin Afb Florida 3
United States Eglin Afb Florida 3
United States Fort Lauderdale Florida 3
United States Fort Lauderdale Florida 3
United States Fort Lauderdale Florida 3
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Fort Walton Beach Florida 12
United States Hollywood Florida 1
United States Jacksonville Florida 8
United States Jacksonville Florida 8
United States Jacksonville Florida 8
United States Jacksonville Florida 8
United States Jacksonville Florida 8
United States Jacksonville Florida 8
United States Jacksonville Florida 8
United States Jupiter Florida 1
United States Lakeland Florida 1
United States Miami Florida 5
United States Miami Florida 5
United States Miami Florida 5
United States Miami Florida 5
United States Miami Florida 5
United States Middleburg Florida 1
United States Naples Florida 5
United States Naples Florida 5
United States Naples Florida 5
United States Naples Florida 5
United States Odessa Florida 4
United States Orlando Florida 4
United States Orlando Florida 4
United States Orlando Florida 4
United States Orlando Florida 4
United States Ormond Beach Florida 1
United States Palm Harbor Florida 1
United States Panama City Florida 1
United States Penney Farms Florida 1
United States Pensacola Florida 6
United States Pensacola Florida 6
United States Pensacola Florida 6
United States Pensacola Florida 6
United States Pensacola Florida 6
United States Pensacola Florida 6
United States Pompano Beach Florida 2
United States Pompano Beach Florida 2
United States Sanford Florida 1
United States Spring Hill Florida 1
United States Tallahassee Florida 1
United States Tampa Florida 1
United States Vernon Florida 1
United States Winter Springs Florida 1
United States Zellwood Florida 1
United States Allenhurst Georgia 2
United States Allenhurst Georgia 2
United States Alpharetta Georgia 1
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Atlanta Georgia 25
United States Augusta Georgia 4
United States Augusta Georgia 4
United States Augusta Georgia 4
United States Augusta Georgia 4
United States Calhoun Georgia 1
United States Carrollton Georgia 1
United States Centerville Georgia 2
United States Cumming Georgia 1
United States Decatur Georgia 2
United States Dublin Georgia 26
United States Duluth Georgia 1
United States Elberton Georgia 1
United States Fayetteville Georgia 5
United States Fitzgerald Georgia 1
United States Fleming Georgia 1
United States Fort Benning Georgia 1
United States Guyton Georgia 1
United States Kennesaw Georgia 1
United States Lawrenceville Georgia 1
United States Lilburn Georgia 1
United States Moultrie Georgia 1
United States Norcross Georgia 2
United States Norcross Georgia 2
United States Savannah Georgia 3
United States Savannah Georgia 3
United States Statesboro Georgia 1
United States Stone Mountain Georgia 2
United States Stone Mountain Georgia 2
United States Boise Idaho 1
United States Rexburg Idaho 1
United States Belleville Illinois 1
United States Berkeley Illinois 1
United States Bloomington Illinois 1
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Bonnie Illinois 32
United States Carbondale Illinois 1
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Chicago Illinois 38
United States Collinsville Illinois 1
United States Deerfield Illinois 1
United States Downers Grove Illinois 1
United States Evanston Illinois 1
United States Greenville Illinois 2
United States Joliet Illinois 3
United States Joliet Illinois 3
United States Lake Forest Illinois 1
United States Mendota Illinois 1
United States Metropolis Illinois 5
United States Metropolis Illinois 5
United States Metropolis Illinois 5
United States Metropolis Illinois 5
United States Metropolis Illinois 5
United States Naperville Illinois 2
United States Naperville Illinois 2
United States North Chicago Illinois 5
United States North Chicago Illinois 5
United States North Chicago Illinois 5
United States North Chicago Illinois 5
United States North Chicago Illinois 5
United States Northbrook Illinois 6
United States Northbrook Illinois 6
United States Northbrook Illinois 6
United States Northbrook Illinois 6
United States Northbrook Illinois 6
United States Northbrook Illinois 6
United States Pittsburg Illinois 3
United States Pittsburg Illinois 3
United States Pittsburg Illinois 3
United States River Forest Illinois 2
United States River Forest Illinois 2
United States Schaumburg Illinois 4
United States Schaumburg Illinois 4
United States Schaumburg Illinois 4
United States Schaumburg Illinois 4
United States Sycamore Illinois 1
United States Urbana Illinois 1
United States Warrenville Illinois 2
United States Warrenville Illinois 2
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States West Frankfort Illinois 12
United States Wood Dale Illinois 1
 
G

Gaurav

Its not easy like this. send the file if you can to (e-mail address removed)
I am leaving for home...will check it when I reach.

although i still think countif should work.
 
G

Gaurav

I got your file....cities are in column B or C?

Gaurav said:
Its not easy like this. send the file if you can to (e-mail address removed)
I am leaving for home...will check it when I reach.

although i still think countif should work.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A For Each in Range... with a .Find(what:="TJ", ... in it problem 15
VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
Vlookup v. drop down box for mailing addresses 1
Frequency Counting 8
vlookup and offset 3
Comparing the values in two cells, then counting how many differ 2
Using the IF and Sheets 0
Counting number of rows based on mutiple criteria 2

Top