Criteria across multiple columns and rows
 

Good evening,

I'm tracking qualificaiton requirements like:

A B C D E
F G
Name Skill 1 Skill 2 Skill 3 Qual Date 1 Qual Date 2
Qual Date 3
Bob Mechanic Safety POL 10 Jan 06
John Mechanic 06 Dec 10
Mack Mechanic HAZMAT 02 Jul 04 10 Aug 09
Tim Manager 15 Mar 99
Fred Manager Computer 10 Sep 08 06 May 07

Job Requirements are in column B, C, and D. Their associated qualification
dates are in column E, F and G. I also use a cell "H1" to help determine
qualification forcasting.

I used a Sumproduct formula to get a number of qualified individuals for
Skill 1:
=Sumproduct(--(B2:B6<""),--(E2:E6<H1).--(E2:E6<""))

This formula works great for determining Skill 1, Skill 2, and Skill 3
independently

I need to determine the total number of "fully qualified" employees.

I need a formula which will only count those individuals who have dates < H1
in all "Required" Skill Sets.

In the above example, only Tim and Fred are the only two fully qualified
individuals.
Bob isn't qualified in Safety or POL
John hasn't gone to school yet
Mack is going to school in August (the future).

I sure hope this question makes sense to someone else besides myself.

Thanks