Prev: excel 101
Next: 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
<PeterGonza...(a)discussions.microsoft.com> 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 101
Next: Automic allocation of numbers