sum week values in different columns

[jeditetsu]

Dear mrexcel community,
I'd like to share my first question in the forum with you.
What I am trying to do is summing up different item quantities by week and put in sheet 2:
[TABLE="width: 206"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]
Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]desciption [/TD]
[TD][/TD]
[TD]Qty[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/01/2018[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2/01/2018[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13/01/2018[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4/04/2018[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20/01/2018[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
[TABLE="width: 334"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]desciption[/TD]
[TD][/TD]
[TD]Week1[/TD]
[TD]Week2[/TD]
[TD]Week3[/TD]
[TD]Week4[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]
[/TD]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD][/TD]
[TD] [/TD]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Item 3 [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
So basically I need a formula to come up with the numbers in red.
Is this possible?
I'd really appreciate your help..

Thanks a lot!
tim

 

[arthurbr]

You first will have to tell us what you mean by "week" ?
There are different definitions of what a week is at http://www.cpearson.com/Excel/WeekNumbers.aspx
If you can tell us what kind of week you need it will be helpful

You can of course use a Pivot Table and define the week type you want to use

 

[jeditetsu]

You first will have to tell us what you mean by "week" ?
There are different definitions of what a week is at http://www.cpearson.com/Excel/WeekNumbers.aspx
If you can tell us what kind of week you need it will be helpful

You can of course use a Pivot Table and define the week type you want to use

Good morning Arthur,

Sorry about that, it would be the week that starts with monday and does not include weekends if possible.

For example for February 2018
week 1 (29 Jan - 02 Feb)
week 2 ( 5 Feb - 11 Feb)
week 3 (12 feb - 18 feb)
week 4 (19 Feb - 25 Feb)
week 5 (26 Feb - 04 March)

hope that helps.

thanks
tim

 

[PaddyD]

1) set up a table somewhere that defines the week number for each date range
2) Add a column to your data that looks up that table to return the appropriate weeknum for each date
3) Use a pivot table to do the maths.

as an aside - "...does not include weekends if possible" - can you guarantee that there are no weekend dates in your source data? If not, you might run the risk of missing entries in the summary calcs.

 

[jeditetsu]

1) set up a table somewhere that defines the week number for each date range
2) Add a column to your data that looks up that table to return the appropriate weeknum for each date
3) Use a pivot table to do the maths.

as an aside - "...does not include weekends if possible" - can you guarantee that there are no weekend dates in your source data? If not, you might run the risk of missing entries in the summary calcs.

hey PaddyD

The file is actually bigger than just 3 items. There are more than 4000 items. And there are multiple rows for the same item so I have to do summing as well.
Basically sheet 1 is data for open orders which is extracted from the system.
Sheet 2 is the S&OP report, and I need to indicate which order is arriving when (which week).

 

[PaddyD]

I'm confused. The approach I outlined would solve the problem as you originally stated it ("What I am trying to do is summing up different item quantities by week and put in sheet 2"). That doesn't sound quite the same as "I need to indicate which order is arriving when".

What problem are you actually trying to solve?