Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Row and Column Criteria
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 Row and Column Criteria
Do you mean
=SUMPRODUCT(--(B2:B5<""),--(D2:D5<""),--(D2:D5<F1),--(E2:E5<""),--(E2:E5<F5)) -- __________________________________ 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Row and Column Criteria
Hi
Try =SUMPRODUCT((B2:B5<"")*(C2:C5<"")*(D2:D5<=F5)* (D2:D5<"")*(E2:E5<=F5)*(E2:E5<"")) -- Regards Roger Govier "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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Row and Column Criteria
I tried that, but it won't count the people who only have one Skill
requirement and only one Date. To test that, deleted all items in "Skill 2" and all Dates in "Date 2". I got a "0" answer. Under this test, I should have gotten "3". (Bob, Tim and Jim are all fully qualified if you remove their "skill 2" entry. If someone is only required to have one skill and only one date, I need to give them credit as fully qualified. If they have two requirements (B and C are Not Null), then the formula needs to look at both dates to qualify the individual. Thoughts? "Roger Govier" wrote: Hi Try =SUMPRODUCT((B2:B5<"")*(C2:C5<"")*(D2:D5<=F5)* (D2:D5<"")*(E2:E5<=F5)*(E2:E5<"")) -- Regards Roger Govier "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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Row and Column Criteria
Hi
Then try this =SUMPRODUCT((B2:B5<"")*(D2:D5<=F1)*(D2:D5<"")) +SUMPRODUCT((C2:C5<"")*(E2:E5<=F1)*(E2:E5<"")) -- Regards Roger Govier "wade04" wrote in message ... I tried that, but it won't count the people who only have one Skill requirement and only one Date. To test that, deleted all items in "Skill 2" and all Dates in "Date 2". I got a "0" answer. Under this test, I should have gotten "3". (Bob, Tim and Jim are all fully qualified if you remove their "skill 2" entry. If someone is only required to have one skill and only one date, I need to give them credit as fully qualified. If they have two requirements (B and C are Not Null), then the formula needs to look at both dates to qualify the individual. Thoughts? "Roger Govier" wrote: Hi Try =SUMPRODUCT((B2:B5<"")*(C2:C5<"")*(D2:D5<=F5)* (D2:D5<"")*(E2:E5<=F5)*(E2:E5<"")) -- Regards Roger Govier "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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Row and Column Criteria
Roger,
Nope, it doesn't work either. This will "qualify" count someone if their Skill 1 date is a future date (not qualified yet) and Skill 2 is a qualifying date. "Roger Govier" wrote: Hi Then try this =SUMPRODUCT((B2:B5<"")*(D2:D5<=F1)*(D2:D5<"")) +SUMPRODUCT((C2:C5<"")*(E2:E5<=F1)*(E2:E5<"")) -- Regards Roger Govier "wade04" wrote in message ... I tried that, but it won't count the people who only have one Skill requirement and only one Date. To test that, deleted all items in "Skill 2" and all Dates in "Date 2". I got a "0" answer. Under this test, I should have gotten "3". (Bob, Tim and Jim are all fully qualified if you remove their "skill 2" entry. If someone is only required to have one skill and only one date, I need to give them credit as fully qualified. If they have two requirements (B and C are Not Null), then the formula needs to look at both dates to qualify the individual. Thoughts? "Roger Govier" wrote: Hi Try =SUMPRODUCT((B2:B5<"")*(C2:C5<"")*(D2:D5<=F5)* (D2:D5<"")*(E2:E5<=F5)*(E2:E5<"")) -- Regards Roger Govier "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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Row and Column Criteria
Hi
Then how about =SUMPRODUCT((B2:B5<"")*(D2:D5<=F1)*(D2:D5<"")*(C 2:C5="")) +SUMPRODUCT((C2:C5<"")*(E2:E5<=F1)*(E2:E5<"")) -- Regards Roger Govier "wade04" wrote in message ... Roger, Nope, it doesn't work either. This will "qualify" count someone if their Skill 1 date is a future date (not qualified yet) and Skill 2 is a qualifying date. "Roger Govier" wrote: Hi Then try this =SUMPRODUCT((B2:B5<"")*(D2:D5<=F1)*(D2:D5<"")) +SUMPRODUCT((C2:C5<"")*(E2:E5<=F1)*(E2:E5<"")) -- Regards Roger Govier "wade04" wrote in message ... I tried that, but it won't count the people who only have one Skill requirement and only one Date. To test that, deleted all items in "Skill 2" and all Dates in "Date 2". I got a "0" answer. Under this test, I should have gotten "3". (Bob, Tim and Jim are all fully qualified if you remove their "skill 2" entry. If someone is only required to have one skill and only one date, I need to give them credit as fully qualified. If they have two requirements (B and C are Not Null), then the formula needs to look at both dates to qualify the individual. Thoughts? "Roger Govier" wrote: Hi Try =SUMPRODUCT((B2:B5<"")*(C2:C5<"")*(D2:D5<=F5)* (D2:D5<"")*(E2:E5<=F5)*(E2:E5<"")) -- Regards Roger Govier "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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Row and Column Criteria
Still Nope.
This formula will count the same person twice if they are qualified with skill 1 and skill 2. I've also tried the following with no help: =if(Sumproduct(--(C2:C5<""),Sumprodcut(--C2:C5<""),--(E2:E5<""),--(E2:E5<=F1),Sumprodcut(--B2:B5<""),--(D2:D5<""),--(D2:D5<=F1) "Roger Govier" wrote: Hi Then how about =SUMPRODUCT((B2:B5<"")*(D2:D5<=F1)*(D2:D5<"")*(C 2:C5="")) +SUMPRODUCT((C2:C5<"")*(E2:E5<=F1)*(E2:E5<"")) -- Regards Roger Govier "wade04" wrote in message ... Roger, Nope, it doesn't work either. This will "qualify" count someone if their Skill 1 date is a future date (not qualified yet) and Skill 2 is a qualifying date. "Roger Govier" wrote: Hi Then try this =SUMPRODUCT((B2:B5<"")*(D2:D5<=F1)*(D2:D5<"")) +SUMPRODUCT((C2:C5<"")*(E2:E5<=F1)*(E2:E5<"")) -- Regards Roger Govier "wade04" wrote in message ... I tried that, but it won't count the people who only have one Skill requirement and only one Date. To test that, deleted all items in "Skill 2" and all Dates in "Date 2". I got a "0" answer. Under this test, I should have gotten "3". (Bob, Tim and Jim are all fully qualified if you remove their "skill 2" entry. If someone is only required to have one skill and only one date, I need to give them credit as fully qualified. If they have two requirements (B and C are Not Null), then the formula needs to look at both dates to qualify the individual. Thoughts? "Roger Govier" wrote: Hi Try =SUMPRODUCT((B2:B5<"")*(C2:C5<"")*(D2:D5<=F5)* (D2:D5<"")*(E2:E5<=F5)*(E2:E5<"")) -- Regards Roger Govier "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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Row and Column Criteria
I disagree.
It only counts the person once if they have both sills within the time, and once if they have a single skill within the time. Send me you actual workbook. To mail direct, send to roger at technology4u dot co dot uk Change the at and dots to make valid email address -- Regards Roger Govier "wade04" wrote in message ... Still Nope. This formula will count the same person twice if they are qualified with skill 1 and skill 2. I've also tried the following with no help: =if(Sumproduct(--(C2:C5<""),Sumprodcut(--C2:C5<""),--(E2:E5<""),--(E2:E5<=F1),Sumprodcut(--B2:B5<""),--(D2:D5<""),--(D2:D5<=F1) "Roger Govier" wrote: Hi Then how about =SUMPRODUCT((B2:B5<"")*(D2:D5<=F1)*(D2:D5<"")*(C 2:C5="")) +SUMPRODUCT((C2:C5<"")*(E2:E5<=F1)*(E2:E5<"")) -- Regards Roger Govier "wade04" wrote in message ... Roger, Nope, it doesn't work either. This will "qualify" count someone if their Skill 1 date is a future date (not qualified yet) and Skill 2 is a qualifying date. "Roger Govier" wrote: Hi Then try this =SUMPRODUCT((B2:B5<"")*(D2:D5<=F1)*(D2:D5<"")) +SUMPRODUCT((C2:C5<"")*(E2:E5<=F1)*(E2:E5<"")) -- Regards Roger Govier "wade04" wrote in message ... I tried that, but it won't count the people who only have one Skill requirement and only one Date. To test that, deleted all items in "Skill 2" and all Dates in "Date 2". I got a "0" answer. Under this test, I should have gotten "3". (Bob, Tim and Jim are all fully qualified if you remove their "skill 2" entry. If someone is only required to have one skill and only one date, I need to give them credit as fully qualified. If they have two requirements (B and C are Not Null), then the formula needs to look at both dates to qualify the individual. Thoughts? "Roger Govier" wrote: Hi Try =SUMPRODUCT((B2:B5<"")*(C2:C5<"")*(D2:D5<=F5)* (D2:D5<"")*(E2:E5<=F5)*(E2:E5<"")) -- Regards Roger Govier "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 | |||
Formula for multiple criteria in a column. | Excel Worksheet Functions | |||
Count if with multiple column criteria | Excel Worksheet Functions | |||
How do I sum column D and F based on multiple column criteria? | Excel Worksheet Functions | |||
Multiple Criteria in same Column | Excel Worksheet Functions | |||
multiple criteria in one column | Excel Worksheet Functions |