Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 207
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 207
Default 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






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15
Default 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








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 207
Default 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








  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15
Default 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









  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 207
Default Count names and occurrences

Its not easy like this. send the file if you can to
I am leaving for home...will check it when I reach.

although i still think countif should work.

"Midjack" wrote in message
...
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











  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 207
Default Count names and occurrences

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

"Gaurav" wrote in message
...
Its not easy like this. send the file if you can to
I am leaving for home...will check it when I reach.

although i still think countif should work.

"Midjack" wrote in message
...
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













Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying names and frqequency of occurrences. pms240 Excel Worksheet Functions 3 March 19th 07 04:10 PM
trying to COUNT occurrences when certain criteria is met Allan from Melbourne Excel Discussion (Misc queries) 4 August 2nd 06 11:01 AM
Count number of occurrences MarkM Excel Discussion (Misc queries) 1 July 27th 06 10:14 PM
how do I count the occurrences of multiple conditions Debi Excel Worksheet Functions 8 July 18th 06 02:28 PM
Count unique occurrences of name jhicsupt Excel Discussion (Misc queries) 4 October 5th 05 05:46 PM


All times are GMT +1. The time now is 05:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"