Multiple Iifs

[Guest]

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!

 

[Dave F]

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!A14,4,false)) may work.

Dave

 

[Guest]

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

 

[Guest]

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!D225). Is the problem because I need a date format?

 

[Roger Govier]

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)