I was working on this one yesterday but it's fallen to page 2 (under the same
subject title), so i posted a followup today.

I would like to be able to pull the average hours over and have them fill in
my spreadsheet under the dates they are being divided by (start & End dates)

On Sheet2 I have all the work centers listed in column A and starting in
column C row 1, I have the dates running sequentially, starting on 3/2/09
and running through the end of the year.

The formula I'm looking for would need to be able to take the available
hours on a given work center from Sheet1and split them up equally between the
start and end date (columns A & B on Sheet1). Then put those hours on the
spreadsheet under the correct work center and under the dates listed on
Sheet2 row 1.

Column A Column B Column C Column D
Column E
Row1 Start Date End Date work center total hours
average hrs
row 2 3/2/09 3/6/09 18 bench 10
2

So on my spreadsheet it would show 2 hours under columns C (3/2/09) through
column G (3/6/09) it would show 2 hours for each day in the row that has work
center (18 bench). There may be multiple start and end dates that overlap
within each work center.

I hope you can help with this!
--
SRC

Can't be done with SUMIF, but I believe this is what you're looking for:
=SUMPRODUCT((Sheet1!$C$2:$C$5=$A2)*(Sheet1!$A$2:$A $5<=B$1)*(Sheet1!$B$2:$B$5=B$1)*(Sheet1!$E$2:$E$5 ))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

I was working on this one yesterday but it's fallen to page 2 (under the same
subject title), so i posted a followup today.

I would like to be able to pull the average hours over and have them fill in
my spreadsheet under the dates they are being divided by (start & End dates)

On Sheet2 I have all the work centers listed in column A and starting in
column C row 1, I have the dates running sequentially, starting on 3/2/09
and running through the end of the year.

The formula I'm looking for would need to be able to take the available
hours on a given work center from Sheet1and split them up equally between the
start and end date (columns A & B on Sheet1). Then put those hours on the
spreadsheet under the correct work center and under the dates listed on
Sheet2 row 1.

Column A Column B Column C Column D
Column E
Row1 Start Date End Date work center total hours
average hrs
row 2 3/2/09 3/6/09 18 bench 10
2

So on my spreadsheet it would show 2 hours under columns C (3/2/09) through
column G (3/6/09) it would show 2 hours for each day in the row that has work
center (18 bench). There may be multiple start and end dates that overlap
within each work center.

I hope you can help with this!
--
SRC