sum week values in different columns

jeditetsu

New Member
Joined
Jan 7, 2018
Messages
5
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:

Sheet 1
desciption QtyDate
Item 151/01/2018
Item 1102/01/2018
Item 21513/01/2018
Item 1504/04/2018
Item 12020/01/2018

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Sheet 2
desciptionWeek1Week2Week3Week4
Item 1
15 20
Item 2 15
Item 3

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,165
Office Version
  1. 2010
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

New Member
Joined
Jan 7, 2018
Messages
5
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Jan 7, 2018
Messages
5
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,372
Messages
5,595,787
Members
414,021
Latest member
whyjaydee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top