How to sum up the value in column based on a 5 day week period?
 

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

How to sum up the value in column based on a 5 day week period?
 

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.