Multiple Criteria across multiple Columns

W

wade04

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.
 
W

wade04

Sorry, I got an error the first time I tried to send it. I didnt' think it
posted. I had to type is again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct across multiple rows and columns?? 0
Multiple Row and Column Criteria 8
Criteria across multiple columns and rows 0
Multiple Criteria Date Count formula 1
Formula Count dates multiple criteria 3
Summing from multiple wksts - incomprehensible #VALUE error 9
Convert multiple rows to multiple columns 3
Show/Restrict records using criteria across on multiple records. 1

Top