Counting multiple occurances of a specific string
 

I am trying to sift through a list of names and cities and determine come up
with a number for each name: 0=no city listed, 1=a city is listed, but not in
my city, and 2=my city.

Example: (my city is Seattle)
Sheet 1 (named "All Players")
Last First City
Jones James Chicago
Smith John Seattle
Doe Jason
Jones Frank Seattle

So, then in sheet 2, it would look like this:
Jones James 1
Smith John 2
Doe Jason 0
Jones Frank 2

I will mannually enter the names in Sheet 2, columns A & B, but I want a
function for automatically generating a number based on column C of sheet 1.
I don't want to reference specific cells because I will be inserting,
deleting, and rearanging several thousand names.

What I have so far is almost working, but I can't quite get the final touch.
If I enter in Seattle in Column C, next to the names in Columns A & B of
Sheet 1, then the corresponding name in Sheet 2 will pop up with a 2 in
column C - just like it is supposed to.... however, I always have a 1 for
names with both a different city, or with no listed city. I cannot get a
blank city to show me a 0.

Here is the formula I have so far:
=SUM(IF('All Players'!$A$1:$A$1309=A3,(IF('All
Players'!$B$1:$B$1309=B3,(IF('All
Players'!$AM$1:$AM$1309="Seattle",2,(IF((COUNTA('A ll
Players'!AM1:AM1309,""))1,1)))))),0))

My logic is that IF the city = "Seattle", then the value-if-true = 2. This
works. However, the value-if-false being another formula of COUNTA must be
counting the entire C column instead of just the corresponding columns of A &
B

Thanks for any help.
Jim

Put in D2 and copy down...........

=IF(C2="seattle",2,IF(LEN(A1)0,1,0))

Vaya con Dios,
Chuck, CABGx3



"BaseballFan" wrote in message
...
I am trying to sift through a list of names and cities and determine come
up
with a number for each name: 0=no city listed, 1=a city is listed, but not
in
my city, and 2=my city.

Example: (my city is Seattle)
Sheet 1 (named "All Players")
Last First City
Jones James Chicago
Smith John Seattle
Doe Jason
Jones Frank Seattle

So, then in sheet 2, it would look like this:
Jones James 1
Smith John 2
Doe Jason 0
Jones Frank 2

I will mannually enter the names in Sheet 2, columns A & B, but I want a
function for automatically generating a number based on column C of sheet
1.
I don't want to reference specific cells because I will be inserting,
deleting, and rearanging several thousand names.

What I have so far is almost working, but I can't quite get the final
touch.
If I enter in Seattle in Column C, next to the names in Columns A & B of
Sheet 1, then the corresponding name in Sheet 2 will pop up with a 2 in
column C - just like it is supposed to.... however, I always have a 1 for
names with both a different city, or with no listed city. I cannot get a
blank city to show me a 0.

Here is the formula I have so far:
=SUM(IF('All Players'!$A$1:$A$1309=A3,(IF('All
Players'!$B$1:$B$1309=B3,(IF('All
Players'!$AM$1:$AM$1309="Seattle",2,(IF((COUNTA('A ll
Players'!AM1:AM1309,""))1,1)))))),0))

My logic is that IF the city = "Seattle", then the value-if-true = 2.
This
works. However, the value-if-false being another formula of COUNTA must
be
counting the entire C column instead of just the corresponding columns of
A &
B

Thanks for any help.
Jim