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