Counting on two conditions, one with a range
 

I have a list of people, their location, and job code like so

starting in a1

dave atlanta 2350
bob new york 2450
jim atlanta 3420
gary chicago 2550
mike atlanta 2450
cindy atlanta 2650
mary atlanta 2125
mark atlanta 2250

I have a list of job codes I want to count by city in e1

2125
2250
2350
2450

And I have a list of cities in h1

Atlanta
Chicago
New York

I want a formula in h2, h3, h4 for each city that will count how many people
are in each city if their job code matches one of the codes in the list in
column e.

I cant count the employees in each city with =sum(if(a1:a10=h1,1,0)) entered
as an array. I can't figure out how to combine that with the job code list
though to see if their job code matches one in my list in column e.

Counting on two conditions, one with a range
 

See this screencap:

http://img407.imageshack.us/img407/5755/sumpwz2.jpg

Enter the formula in F2. Copy across to H2 then down.

Biff

"Ted Metro" wrote in message
...
I have a list of people, their location, and job code like so

starting in a1

dave atlanta 2350
bob new york 2450
jim atlanta 3420
gary chicago 2550
mike atlanta 2450
cindy atlanta 2650
mary atlanta 2125
mark atlanta 2250

I have a list of job codes I want to count by city in e1

2125
2250
2350
2450

And I have a list of cities in h1

Atlanta
Chicago
New York

I want a formula in h2, h3, h4 for each city that will count how many
people
are in each city if their job code matches one of the codes in the list in
column e.

I cant count the employees in each city with =sum(if(a1:a10=h1,1,0))
entered
as an array. I can't figure out how to combine that with the job code
list
though to see if their job code matches one in my list in column e.

Counting on two conditions, one with a range
 

One way ..

Source table assumed in A1:C8, E1:E4 holds the input job codes of interest.
In H1 down is the city input, say: Atlanta, Chicago, etc

Place in I1:
=SUMPRODUCT((ISNUMBER(MATCH($C$1:$C$8,$E$1:$E$4,0) )*($B$1:$B$8=H1)))
Copy I1 down. Col I returns the required results for the city in col H.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ted Metro" wrote:
I have a list of people, their location, and job code like so

starting in a1

dave atlanta 2350
bob new york 2450
jim atlanta 3420
gary chicago 2550
mike atlanta 2450
cindy atlanta 2650
mary atlanta 2125
mark atlanta 2250

I have a list of job codes I want to count by city in e1

2125
2250
2350
2450

And I have a list of cities in h1

Atlanta
Chicago
New York

I want a formula in h2, h3, h4 for each city that will count how many people
are in each city if their job code matches one of the codes in the list in
column e.

I cant count the employees in each city with =sum(if(a1:a10=h1,1,0)) entered
as an array. I can't figure out how to combine that with the job code list
though to see if their job code matches one in my list in column e.

Counting on two conditions, one with a range
 

Thank you Max & Biff!!!!!

"Max" wrote:

One way ..

Source table assumed in A1:C8, E1:E4 holds the input job codes of interest.
In H1 down is the city input, say: Atlanta, Chicago, etc

Place in I1:
=SUMPRODUCT((ISNUMBER(MATCH($C$1:$C$8,$E$1:$E$4,0) )*($B$1:$B$8=H1)))
Copy I1 down. Col I returns the required results for the city in col H.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ted Metro" wrote:
I have a list of people, their location, and job code like so

starting in a1

dave atlanta 2350
bob new york 2450
jim atlanta 3420
gary chicago 2550
mike atlanta 2450
cindy atlanta 2650
mary atlanta 2125
mark atlanta 2250

I have a list of job codes I want to count by city in e1

2125
2250
2350
2450

And I have a list of cities in h1

Atlanta
Chicago
New York

I want a formula in h2, h3, h4 for each city that will count how many people
are in each city if their job code matches one of the codes in the list in
column e.

I cant count the employees in each city with =sum(if(a1:a10=h1,1,0)) entered
as an array. I can't figure out how to combine that with the job code list
though to see if their job code matches one in my list in column e.

Counting on two conditions, one with a range
 

Welcome, Ted.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ted Metro" wrote in message
...
Thank you Max & Biff !!!!!