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.

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.

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.

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.

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.

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.

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.