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

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

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

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

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

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