Prev: excel 101Next: Automic allocation of numbers From: Peter Gonzalez on 2 Mar 2010 13:42 Hello, Im trying to add the amount of time spent on a type of machinery based on a 5 day week period. For example from 3/1/2010 to 3/5/2010 not including weekends. On the sheet with the data of the amount of time I need added is layed out like this: Colomn A Colomn B Colomn K Example:3/1/2010 PL 1 1:54 3/1/2010 PL 5 0:13 3/1/2010 PL 1 0:07 3/2/2010 PL 5 0:13 3/2/2010 PL 1 1:00 3/2/2010 PL 5 0:30 and so on... On the sheet where the data is to be added and placed to populate my chart is layed out like this: The dates are based on the date of the ending week period. Colomn A Colomn B Colomn C Colomn D 3/5/2010 3/12/2010 3/19/2010 PL 1 PL 2 PL 3 PL 4 PL 5 How can i get the info to add up for a week without having to do use the SUMIF or SUMIFS based formula selecting the number of cells used for the 5 day week period at the end of each week? Let me know if the information I provided was enough to make my self clear. Your help is greatly appreciated. Thanks Peter From: Per Jessen on 2 Mar 2010 16:29 Hi Peter, Suppose you have original data on sheet 1 and want to calculate 'chart data' on sheet 2, insert this formula in B2 of sheet2, and copy/paste the formula to fill the entire table: =SUMPRODUCT(--(Sheet1!\$A\$1:\$A\$100>=Sheet2!B\$1-5);--(Sheet1!\$A\$1:\$A \$100<=Sheet2!B\$1);--(Sheet1!\$B\$1:\$B\$100=Sheet2!\$A2);Sheet1!\$K\$1:\$K \$100) Regards, Per On 2 Mar., 19:42, Peter Gonzalez wrote:> Hello, > Im trying to add the amount of time spent on a type of machinery based on a   > 5 day week period. For example from 3/1/2010 to 3/5/2010 not including > weekends. > On the sheet with the data of the amount of time I need added is layed out > like this: >              Colomn A          Colomn B           Colomn K > Example:3/1/2010              PL 1                    1:54 >              3/1/2010              PL 5                    0:13 >              3/1/2010              PL 1                    0:07 >              3/2/2010              PL 5                    0:13 >              3/2/2010              PL 1                    1:00 >              3/2/2010              PL 5                    0:30 > > and so on... > > On the sheet where the data is to be added and placed to populate my chart > is layed out like this: > The dates are based on the date of the ending week period. > Colomn A           Colomn B         Colomn C           Colomn D >                          3/5/2010         3/12/2010          3/19/2010 > PL 1 > PL 2 > PL 3 > PL 4 > PL 5 > > How can i get the info to add up for a week without having to do use the > SUMIF or SUMIFS based formula selecting the number of cells used for the 5 > day week period at the end of each week? > Let me know if the information I provided was enough to make my self clear. > Your help is greatly appreciated. > Thanks > Peter  |  Pages: 1 Prev: excel 101Next: Automic allocation of numbers