Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria across multiple Columns
Good Evening
I have a spreadsheet like: A B C D E Name Skill 1 Skill 2 Date 1 Date 2 Bob Mechanic POL 11JAN06 04MAY09 Tim Mechanic HAZMAT 04JUN08 Ed Manager Safety 10AUG10 Jim Manager 10APR07 I wrote a Sumproduct formula to track the number of people who are qualified in their skills (Skill 1 qual date is associated to Date 1 and Skill 2 is associated to Date 2). It looks something like =Sumproduct(--(B2:B5<""),--(D2:D5<F1),--(D2:D5<""). NOTE: F5 is a date for forcasting This worked great to determine the number of people who were Skill 1 or Still 2 qualified. I now need to figure out who is fully qualified. Who has dates for Skill 1 and Skill 2 which are < F5. Any idea how I can tie the rows into the Sumproduct formula to add that extra dimension? In the above example, only Jim is qualified. (Answer is 1) Bob is not qualified for POL yet. He has a future date Tim is not HAZMAT qualified Ed is not a qualified manager and hasn't gone to Safety yet. I sure hope this makes sense to someone else besides me. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria across multiple Columns
Sorry, I got an error the first time I tried to send it. I didnt' think it
posted. I had to type is again. "wade04" wrote: Good Evening I have a spreadsheet like: A B C D E Name Skill 1 Skill 2 Date 1 Date 2 Bob Mechanic POL 11JAN06 04MAY09 Tim Mechanic HAZMAT 04JUN08 Ed Manager Safety 10AUG10 Jim Manager 10APR07 I wrote a Sumproduct formula to track the number of people who are qualified in their skills (Skill 1 qual date is associated to Date 1 and Skill 2 is associated to Date 2). It looks something like =Sumproduct(--(B2:B5<""),--(D2:D5<F1),--(D2:D5<""). NOTE: F5 is a date for forcasting This worked great to determine the number of people who were Skill 1 or Still 2 qualified. I now need to figure out who is fully qualified. Who has dates for Skill 1 and Skill 2 which are < F5. Any idea how I can tie the rows into the Sumproduct formula to add that extra dimension? In the above example, only Jim is qualified. (Answer is 1) Bob is not qualified for POL yet. He has a future date Tim is not HAZMAT qualified Ed is not a qualified manager and hasn't gone to Safety yet. I sure hope this makes sense to someone else besides me. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria across multiple Columns
I replied to one of the other many multiple threads (MS forum problem!)
-- __________________________________ HTH Bob "wade04" wrote in message ... Good Evening I have a spreadsheet like: A B C D E Name Skill 1 Skill 2 Date 1 Date 2 Bob Mechanic POL 11JAN06 04MAY09 Tim Mechanic HAZMAT 04JUN08 Ed Manager Safety 10AUG10 Jim Manager 10APR07 I wrote a Sumproduct formula to track the number of people who are qualified in their skills (Skill 1 qual date is associated to Date 1 and Skill 2 is associated to Date 2). It looks something like =Sumproduct(--(B2:B5<""),--(D2:D5<F1),--(D2:D5<""). NOTE: F5 is a date for forcasting This worked great to determine the number of people who were Skill 1 or Still 2 qualified. I now need to figure out who is fully qualified. Who has dates for Skill 1 and Skill 2 which are < F5. Any idea how I can tie the rows into the Sumproduct formula to add that extra dimension? In the above example, only Jim is qualified. (Answer is 1) Bob is not qualified for POL yet. He has a future date Tim is not HAZMAT qualified Ed is not a qualified manager and hasn't gone to Safety yet. I sure hope this makes sense to someone else besides me. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To count the data using multiple criteria in multiple columns | New Users to Excel | |||
create new table from Multiple Criteria in multiple columns | Excel Worksheet Functions | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
Formula to sum multiple columns on multiple criteria | Excel Discussion (Misc queries) | |||
Multiple Criteria and Multiple Columns | Excel Discussion (Misc queries) |