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]

I think you are saying you want a formula for O1 as well as O2? Here's one way you could do it.

	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	31 Oct 2020	30 Nov 2020	31 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
Sheet1

Cell Formulas
Range	Formula
O1:Q1	O1	=UNIQUE(EOMONTH(0+MyWeeks,0),1)
O2:Q2	O2	=SUM((EOMONTH(0+MyWeeks,0)=O1)*MyValues)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyValues	=Sheet1!$B$2:$N$2	O2:Q2
MyWeeks	=Sheet1!$B$1:$N$1	O2:Q2, O1

 

[ArunS]

I think you are saying you want a formula for O1 as well as O2? Here's one way you could do it.

	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	31 Oct 2020	30 Nov 2020	31 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
Sheet1

Cell Formulas
Range	Formula
O1:Q1	O1	=UNIQUE(EOMONTH(0+MyWeeks,0),1)
O2:Q2	O2	=SUM((EOMONTH(0+MyWeeks,0)=O1)*MyValues)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
MyValues	=Sheet1!$B$2:$N$2	O2:Q2
MyWeeks	=Sheet1!$B$1:$N$1	O2:Q2, O1

Thank you Stephen. Its working now.