I'm trying to do a calculation with multiple Ifs.

On Sheet2, I have the following:

Employee Date Started Date End Hours Available
John Doe 4/1/2007 6/1/2007 30
Joe Smith 4/22/2007 10/1/2007 25
John Doe 6/2/2007 10/1/2007 15

On Sheet1, I have the employees' names, Week Ending Date (which is a Sunday)
and a column where I need the calculation. So it looks like this:

Employee Week Ending Hours Available
John Doe 4/8/2007
John Doe 4/15/2007
John Doe 4/22/2007
Joe Smith 4/8/2007
Joe Smith 4/15/2007
Joe Smith 4/22/2007

I am trying to put the Hours Available between the Date Started and Date End
on Sheet1.

So in this instance, John Doe would have 30 hours for 4/8, 4/15 and 4/22;
Joe Smith would have 25 hours for 4/22/07.

Is there a way to do this? Help!

On May 3, 7:52 am, jhicsupt
wrote:
I'm trying to do a calculation with multiple Ifs.

On Sheet2, I have the following:

Employee Date Started Date End Hours Available
John Doe 4/1/2007 6/1/2007 30
Joe Smith 4/22/2007 10/1/2007 25
John Doe 6/2/2007 10/1/2007 15

On Sheet1, I have the employees' names, Week Ending Date (which is a Sunday)
and a column where I need the calculation. So it looks like this:

Employee Week Ending Hours Available
John Doe 4/8/2007
So what's the criteria here? If the Week Ending date on Sheet1 is
equal to or greater than the Date Started AND equal to or less than
the Date end, then put in the hours available?

If that is correct, something like =IF(AND(B1=Sheet2!B2,B1<=Sheet2!
C2),VLOOKUP(B1,Sheet2!A1:D4,4,false)) may work.

Dave

John Doe 4/15/2007
John Doe 4/22/2007
Joe Smith 4/8/2007
Joe Smith 4/15/2007
Joe Smith 4/22/2007

I am trying to put the Hours Available between the Date Started and Date End
on Sheet1.

So in this instance, John Doe would have 30 hours for 4/8, 4/15 and 4/22;
Joe Smith would have 25 hours for 4/22/07.

Is there a way to do this? Help!

Thanks for the response. That formula returns a False. What I am trying to
do is get the hours available per week within the Date Start and Date End
columns. So if the date start is 5/1 and the date end is 10/1 and the hours
available per week is 30, then for all of the rows in Sheet1 with the dates
between 5/1 and 10/1 would be 30 hours available. (i.e. 30 hours for wk
ending 5/6; 30 hours for wk ending 5/13; 30 hours for wk ending 5/20). On
Sheet1 I have all of the employees names and a row for each wk ending date.
I need it in this format because I am merging it with something else.

Help help

"Dave F" wrote:

On May 3, 7:52 am, jhicsupt
wrote:
I'm trying to do a calculation with multiple Ifs.

On Sheet2, I have the following:

Employee Date Started Date End Hours Available
John Doe 4/1/2007 6/1/2007 30
Joe Smith 4/22/2007 10/1/2007 25
John Doe 6/2/2007 10/1/2007 15

On Sheet1, I have the employees' names, Week Ending Date (which is a Sunday)
and a column where I need the calculation. So it looks like this:

Employee Week Ending Hours Available
John Doe 4/8/2007
So what's the criteria here? If the Week Ending date on Sheet1 is
equal to or greater than the Date Started AND equal to or less than
the Date end, then put in the hours available?

If that is correct, something like =IF(AND(B1=Sheet2!B2,B1<=Sheet2!
C2),VLOOKUP(B1,Sheet2!A1:D4,4,false)) may work.

Dave

John Doe 4/15/2007
John Doe 4/22/2007
Joe Smith 4/8/2007
Joe Smith 4/15/2007
Joe Smith 4/22/2007

I am trying to put the Hours Available between the Date Started and Date End
on Sheet1.

So in this instance, John Doe would have 30 hours for 4/8, 4/15 and 4/22;
Joe Smith would have 25 hours for 4/22/07.

Is there a way to do this? Help!

I tried this formula, but am returning a 0:
=SUMPRODUCT(Sheet2!A2:A25=Sheet1!A2,Sheet2!B2:B25 =Sheet1!B2,Sheet2!C2:C25<Sheet1!C2,Sheet2!D2:D25). Is the problem because I need a date format?

"jhicsupt" wrote:

Thanks for the response. That formula returns a False. What I am trying to
do is get the hours available per week within the Date Start and Date End
columns. So if the date start is 5/1 and the date end is 10/1 and the hours
available per week is 30, then for all of the rows in Sheet1 with the dates
between 5/1 and 10/1 would be 30 hours available. (i.e. 30 hours for wk
ending 5/6; 30 hours for wk ending 5/13; 30 hours for wk ending 5/20). On
Sheet1 I have all of the employees names and a row for each wk ending date.
I need it in this format because I am merging it with something else.

Help help

"Dave F" wrote:

On May 3, 7:52 am, jhicsupt
wrote:
I'm trying to do a calculation with multiple Ifs.

On Sheet2, I have the following:

Employee Date Started Date End Hours Available
John Doe 4/1/2007 6/1/2007 30
Joe Smith 4/22/2007 10/1/2007 25
John Doe 6/2/2007 10/1/2007 15

On Sheet1, I have the employees' names, Week Ending Date (which is a Sunday)
and a column where I need the calculation. So it looks like this:

Employee Week Ending Hours Available
John Doe 4/8/2007
So what's the criteria here? If the Week Ending date on Sheet1 is
equal to or greater than the Date Started AND equal to or less than
the Date end, then put in the hours available?

If that is correct, something like =IF(AND(B1=Sheet2!B2,B1<=Sheet2!
C2),VLOOKUP(B1,Sheet2!A1:D4,4,false)) may work.

Dave

John Doe 4/15/2007
John Doe 4/22/2007
Joe Smith 4/8/2007
Joe Smith 4/15/2007
Joe Smith 4/22/2007

I am trying to put the Hours Available between the Date Started and Date End
on Sheet1.

So in this instance, John Doe would have 30 hours for 4/8, 4/15 and 4/22;
Joe Smith would have 25 hours for 4/22/07.

Is there a way to do this? Help!

Hi

Try
=SUMPRODUCT((Sheet2!$A$2:$A$5=Sheet1!A2)*(Sheet2!$ B$2:$B$45<=Sheet1!B2)*
(Sheet2!$C$2:$C$5=Sheet1!B2)*Sheet2!$D$2:$D$5)

--
Regards

Roger Govier


"jhicsupt" wrote in message
...
I tried this formula, but am returning a 0:
=SUMPRODUCT(Sheet2!A2:A25=Sheet1!A2,Sheet2!B2:B25 =Sheet1!B2,Sheet2!C2:C25<Sheet1!C2,Sheet2!D2:D25).
Is the problem because I need a date format?

"jhicsupt" wrote:

Thanks for the response. That formula returns a False. What I am
trying to
do is get the hours available per week within the Date Start and Date
End
columns. So if the date start is 5/1 and the date end is 10/1 and
the hours
available per week is 30, then for all of the rows in Sheet1 with the
dates
between 5/1 and 10/1 would be 30 hours available. (i.e. 30 hours for
wk
ending 5/6; 30 hours for wk ending 5/13; 30 hours for wk ending
5/20). On
Sheet1 I have all of the employees names and a row for each wk ending
date.
I need it in this format because I am merging it with something else.

Help help

"Dave F" wrote:

On May 3, 7:52 am, jhicsupt
wrote:
I'm trying to do a calculation with multiple Ifs.

On Sheet2, I have the following:

Employee Date Started Date End Hours
Available
John Doe 4/1/2007 6/1/2007
30
Joe Smith 4/22/2007 10/1/2007 25
John Doe 6/2/2007 10/1/2007
15

On Sheet1, I have the employees' names, Week Ending Date (which
is a Sunday)
and a column where I need the calculation. So it looks like
this:

Employee Week Ending Hours Available
John Doe 4/8/2007
So what's the criteria here? If the Week Ending date on Sheet1 is
equal to or greater than the Date Started AND equal to or less than
the Date end, then put in the hours available?

If that is correct, something like
=IF(AND(B1=Sheet2!B2,B1<=Sheet2!
C2),VLOOKUP(B1,Sheet2!A1:D4,4,false)) may work.

Dave

John Doe 4/15/2007
John Doe 4/22/2007
Joe Smith 4/8/2007
Joe Smith 4/15/2007
Joe Smith 4/22/2007

I am trying to put the Hours Available between the Date Started
and Date End
on Sheet1.

So in this instance, John Doe would have 30 hours for 4/8, 4/15
and 4/22;
Joe Smith would have 25 hours for 4/22/07.

Is there a way to do this? Help!