|
Count names and occurrences
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 |
Count names and occurrences
in C2, type =COUNTIF(A:A,A1) and copy down.
is this what you want? I am just wondering that when you copy the furmula down and a city is repeated in column A, the number will also be repeated in column C. Would you be ok with that? "Midjack" wrote in message ... 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 |
Count names and occurrences
Thanks Gaurav. That kinda works if I sort the city, state first, but I have a
list of several thousand cities, and I was hoping that I could wind up with one line per city with a total on the same line. "Gaurav" wrote: in C2, type =COUNTIF(A:A,A1) and copy down. is this what you want? I am just wondering that when you copy the furmula down and a city is repeated in column A, the number will also be repeated in column C. Would you be ok with that? "Midjack" wrote in message ... 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 |
Count names and occurrences
If I understand correct....select the entire range where you have cities, go
to DATA | FILTER | ADVANCED FILTER, check COPY TO OTHER LOCATION and UNIQUE RECORDS ONLY, in COPY TO box, type say..G2, press OK. now you have all the unique cities in column G..means no city is repeated. Now in H2 type =COUNTIF(A:A,G2) and copy down. I think this should give you what you want. "Midjack" wrote in message ... Thanks Gaurav. That kinda works if I sort the city, state first, but I have a list of several thousand cities, and I was hoping that I could wind up with one line per city with a total on the same line. "Gaurav" wrote: in C2, type =COUNTIF(A:A,A1) and copy down. is this what you want? I am just wondering that when you copy the furmula down and a city is repeated in column A, the number will also be repeated in column C. Would you be ok with that? "Midjack" wrote in message ... 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 |
Count names and occurrences
I tried that and got a list of unique cities, but when I ran =COUNTIF(A:A,G2)
all I got were 0's. "Gaurav" wrote: If I understand correct....select the entire range where you have cities, go to DATA | FILTER | ADVANCED FILTER, check COPY TO OTHER LOCATION and UNIQUE RECORDS ONLY, in COPY TO box, type say..G2, press OK. now you have all the unique cities in column G..means no city is repeated. Now in H2 type =COUNTIF(A:A,G2) and copy down. I think this should give you what you want. "Midjack" wrote in message ... Thanks Gaurav. That kinda works if I sort the city, state first, but I have a list of several thousand cities, and I was hoping that I could wind up with one line per city with a total on the same line. "Gaurav" wrote: in C2, type =COUNTIF(A:A,A1) and copy down. is this what you want? I am just wondering that when you copy the furmula down and a city is repeated in column A, the number will also be repeated in column C. Would you be ok with that? "Midjack" wrote in message ... 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 |
Count names and occurrences
well....that should not happen. send me the file if you can.
"Midjack" wrote in message ... I tried that and got a list of unique cities, but when I ran =COUNTIF(A:A,G2) all I got were 0's. "Gaurav" wrote: If I understand correct....select the entire range where you have cities, go to DATA | FILTER | ADVANCED FILTER, check COPY TO OTHER LOCATION and UNIQUE RECORDS ONLY, in COPY TO box, type say..G2, press OK. now you have all the unique cities in column G..means no city is repeated. Now in H2 type =COUNTIF(A:A,G2) and copy down. I think this should give you what you want. "Midjack" wrote in message ... Thanks Gaurav. That kinda works if I sort the city, state first, but I have a list of several thousand cities, and I was hoping that I could wind up with one line per city with a total on the same line. "Gaurav" wrote: in C2, type =COUNTIF(A:A,A1) and copy down. is this what you want? I am just wondering that when you copy the furmula down and a city is repeated in column A, the number will also be repeated in column C. Would you be ok with that? "Midjack" wrote in message ... 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 |
Count names and occurrences
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 "Gaurav" wrote: well....that should not happen. send me the file if you can. "Midjack" wrote in message ... I tried that and got a list of unique cities, but when I ran =COUNTIF(A:A,G2) all I got were 0's. "Gaurav" wrote: If I understand correct....select the entire range where you have cities, go to DATA | FILTER | ADVANCED FILTER, check COPY TO OTHER LOCATION and UNIQUE RECORDS ONLY, in COPY TO box, type say..G2, press OK. now you have all the unique cities in column G..means no city is repeated. Now in H2 type =COUNTIF(A:A,G2) and copy down. I think this should give you what you want. "Midjack" wrote in message ... Thanks Gaurav. That kinda works if I sort the city, state first, but I have a list of several thousand cities, and I was hoping that I could wind up with one line per city with a total on the same line. "Gaurav" wrote: in C2, type =COUNTIF(A:A,A1) and copy down. is this what you want? I am just wondering that when you copy the furmula down and a city is repeated in column A, the number will also be repeated in column C. Would you be ok with that? "Midjack" wrote in message ... 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 |
Count names and occurrences
|
Count names and occurrences
|
| All times are GMT +1. The time now is 06:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com