Summarizing Daily Data into Monthly and then Copy Across Months

[alexvolodarsky]

Hi,

I have a 684x684 data set for daily retention metrics thats that I want to consolidate into Monthly.

	1/1/15	1/2/15	1/3/15	1/4/15	...	...	10/27/16	10/28/16	10/29/16
1/1/15	10	9	8	7	6	6	3	2	2
1/2/15	-	12	10	8	7	7	4	3	2
1/3/15	-	-	14	13	12	11	5	4	3
1/4/15	-	-	-	16	15	10	6	5	5
...	-	-	-	-	18	17	10	9	9
...	-	-	-	-	-	20	6	5	5
10/27/16	-	-	-	-	-	-	31	30	29
10/28/16	-	-	-	-	-	-	-	35	33
10/29/16	-	-	-	-	-	-	-	-	36

<tbody>
</tbody>

The resulting table should look like this

	31	28	31	30	31	30			31	31	30	31
	Jan 2015	Feb 2015	Mar 2015	Apr 2015	May 2015	Jun 2015	...	...	July 2016	Aug 2016	Sept 2016	Oct 2016
Jan 2015	Sum of Jan daily	800	700	600	500	400	375	350	300	200	150	100
Feb 2015		1200	1100	1000	900	800	700	600	500	400	300	200
March 2015			...	...	...	...	...	...	...	...	...	...
Apr 2015				...	...	...	...	...	...	...	...	...
May 2015					...	...	...	...	...	...	...	...
Jun 2015						...	...	...	...	...	...	...
...							1500	1400	1300	1200	1100	1000
...								1600	1500	1400	1300	1200
July 2016									1700	1600	1500	1400
Aug 2016										1800	1700	1600
Sept 2016											1900	1800
Oct 2016												2000

<tbody>
</tbody>

For example, the formula for January value in monthly table should be sum(Daily!B2:Daily!AF32). I want to create a formula which I can drag on the monthly table. I assume I'll have use some combination of index and match, and add a row above months with the number of days in the month to adjust the position of the daily table to sum the correct amount (e.g. the table should move over 28 not 31 from January to February).

Does anyone have any ideas how to construct this formula??

 

[CyrusTheVirus]

Do you create your data in that manner or does it come to you in that manner?

Also, what is the point of having the dates duplicated on the top and the side?

Ideally, you would want your data to be formatted like below. Then you could use a pivot table to easily do what you want.

Month	Date	Amount
January	1/1/2016	187
January	1/2/2016	387
January	1/5/2016	77
January	1/16/2016	487
January	1/17/2016	395
October	10/12/2016	195
October	10/15/2016	362
October	10/22/2016	412

<tbody>
</tbody>

 

[SpillerBD]

SUMIFS and SUMPRODUCT will be your primary formula solutions.
However, Pivot Tables have a grouping function for dates that works great too.

You might check out EXCELISFUN channel on Youtube and the video helps from Mr Excel