Automation of weekly buckets to respective monthly buckets

[ArunS]

Hi,

I would like to understand if there is a formula to:

1. Convert Weeks of a month into a monthly bucket.Below is an example. I need all these weeks to be unified into its respective month.

2. There are volumes under each week and I want to sum up the volumes from these weeks into its respective month.

May I know if there is an excel formula for having the combinations of point# 1 and 2? I would be a great help for me.
This is just an example and I am looking to replicate this formula to a wide range of weeks to months and then volume of these weeks into its respective months.

 

[StephenCrump]

What's your link between weeks and months?

Are 4 October, 11 October etc week start dates, or week end dates?

What months do 27 September and 1 November fall into, and why?

 

[ArunS]

Hi Stephen,

Just to mention, the point was not about whether the week's date, is a start date or an end date of a week.
It's more to do with accumulating all weeks of a particular month, into a Month bucket and subsequently summing the volumes mentioned in the weeks to that particular month.

The link between the weeks and months are the volume within the weeks of a month.
I hope I made my part clear.

What's your link between weeks and months?

Are 4 October, 11 October etc week start dates, or week end dates?

What months do 27 September and 1 November fall into, and

 

[StephenCrump]

Like this?

	A	B	C	D	E	F	G	H	I
1		27 Sep 2020	4 Oct 2020	11 Oct 2020	18 Oct 2020	25 Oct 2020	1 Nov 2020	8 Nov 2020	15 Nov 2020
2		1	2	3	5	7	9	11	13
3
4	Sep 2020	1
5	Oct 2020	17
6	Nov 2020	33
Sheet2

Cell Formulas
Range	Formula
B4:B6	B4	=SUMPRODUCT(--(EOMONTH(0+B$1:I$1,0)=EOMONTH(A4,0)),B$2:I$2)

 

[ArunS]

Like this?

	A	B	C	D	E	F	G	H	I
1		27 Sep 2020	4 Oct 2020	11 Oct 2020	18 Oct 2020	25 Oct 2020	1 Nov 2020	8 Nov 2020	15 Nov 2020
2		1	2	3	5	7	9	11	13
3
4	Sep 2020	1
5	Oct 2020	17
6	Nov 2020	33
Sheet2

Cell Formulas
Range	Formula
B4:B6	B4	=SUMPRODUCT(--(EOMONTH(0+B$1:I$1,0)=EOMONTH(A4,0)),B$2:I$2)

Let me try this formula.Ideally I would like to have the months in horizontal format and volumes summed below them

 

[ArunS]

Let me try this formula.Ideally I would like to have the months in horizontal format and volumes summed below them

Hi Stephen,

The formula is not sufficing what I am looking for.
Because what I need as an output from the formula should be like what is shown in green shaded.
Basically having a formula that will be a combination of summing weeks to its respective"MMM-YY" and also subsequently summing the volumes within those weeks.

 

[StephenCrump]

What does not sufficing mean? You need help putting it in your format? The formula is producing an error?

Here it is in your format. Note that O1:Q1 are dates (just formated as MMM YYYY). If it's not working for you, please be specific about what's happening.

Book9.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Weeks	4 Oct 2020	11 Oct 2020	18 Oct 2020	25 Oct 2020	1 Nov 2020	8 Nov 2020	15 Nov 2020	22 Nov 2020	29 Nov 2020	6 Dec 2020	13 Dec 2020	20 Dec 2020	27 Dec 2020	Oct 2020	Nov 2020	Dec 2020
2	Vol	67,593	67,375	68,290	68,463	74,137	70,746	72,576	68,859	69,102	69,234	69,360	68,580	68,226	271,721	355,420	275,400
Sheet2

Cell Formulas
Range	Formula
O2:Q2	O2	=SUMPRODUCT(--(EOMONTH(0+$B1:$N1,0)=EOMONTH(O1,0)),$B2:$N2)

 

[ArunS]

What does not sufficing mean? You need help putting it in your format? The formula is producing an error?

Here it is in your format. Note that O1:Q1 are dates (just formated as MMM YYYY). If it's not working for you, please be specific about what's happening.

Book9.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Weeks	4 Oct 2020	11 Oct 2020	18 Oct 2020	25 Oct 2020	1 Nov 2020	8 Nov 2020	15 Nov 2020	22 Nov 2020	29 Nov 2020	6 Dec 2020	13 Dec 2020	20 Dec 2020	27 Dec 2020	Oct 2020	Nov 2020	Dec 2020
2	Vol	67,593	67,375	68,290	68,463	74,137	70,746	72,576	68,859	69,102	69,234	69,360	68,580	68,226	271,721	355,420	275,400
Sheet2

Cell Formulas
Range	Formula
O2:Q2	O2	=SUMPRODUCT(--(EOMONTH(0+$B1:$N1,0)=EOMONTH(O1,0)),$B2:$N2)

Hello Stephen,

Thanks for your support. I guess I will start from the beginning on what I need as help regarding this formula.

I want a formula that will be a combination of:

Converting the weeks of the month into a month summation(not just a formatting, but using a formula that will cummulate the weeks , to a month total)

Second, to induce into the same formula, the summation of the volumes from the weeks of a month, into a month total volume.

To conclude the output as a result of using that formula should be as shown below in green shade.

Regards,
Arun

 

[StephenCrump]

What version of Excel do you have?

 

[ArunS]

What version of Excel do you have?

I have Office 365