| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 !!!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HELP!!! Counting Selective Responses if 2 conditions are met? | Excel Worksheet Functions | |||
| Counting a Column when Three Conditions are true | Excel Worksheet Functions | |||
| Counting Text Strings With Conditions | Excel Discussion (Misc queries) | |||
| counting cells in a data range that meet 3 specific conditions | Excel Discussion (Misc queries) | |||
| counting based on 2 conditions | Excel Discussion (Misc queries) | |||
Linear Mode
