Counting on two conditions, one with a range

[Ted Metro]

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.

 

[T. Valko]

See this screencap:

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

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

Biff

 

[Max]

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.

 

[Ted Metro]

Thank you Max & Biff!!!!!

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]

Welcome, Ted.