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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1/1/15[/TD]
[TD]1/2/15[/TD]
[TD]1/3/15[/TD]
[TD]1/4/15[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]10/27/16[/TD]
[TD]10/28/16[/TD]
[TD]10/29/16[/TD]
[/TR]
[TR]
[TD]1/1/15[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/2/15[/TD]
[TD]-[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/3/15[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]14[/TD]
[TD]13[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/4/15[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]16[/TD]
[TD]15[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]20[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]10/27/16[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]10/28/16[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]35[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]10/29/16[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]36[/TD]
[/TR]
</tbody>[/TABLE]

The resulting table should look like this[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]31[/TD]
[TD]28[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan 2015[/TD]
[TD]Feb 2015[/TD]
[TD]Mar 2015[/TD]
[TD]Apr 2015[/TD]
[TD]May 2015[/TD]
[TD]Jun 2015[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]July 2016[/TD]
[TD]Aug 2016[/TD]
[TD]Sept 2016[/TD]
[TD]Oct 2016[/TD]
[/TR]
[TR]
[TD]Jan 2015[/TD]
[TD]Sum of Jan daily[/TD]
[TD]800[/TD]
[TD]700[/TD]
[TD]600[/TD]
[TD]500[/TD]
[TD]400[/TD]
[TD]375[/TD]
[TD]350[/TD]
[TD]300[/TD]
[TD]200[/TD]
[TD]150[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Feb 2015[/TD]
[TD][/TD]
[TD]1200[/TD]
[TD]1100[/TD]
[TD]1000[/TD]
[TD]900[/TD]
[TD]800[/TD]
[TD]700[/TD]
[TD]600[/TD]
[TD]500[/TD]
[TD]400[/TD]
[TD]300[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]March 2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Apr 2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]May 2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]Jun 2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD]...[/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]
[TD][/TD]
[TD]1500[/TD]
[TD]1400[/TD]
[TD]1300[/TD]
[TD]1200[/TD]
[TD]1100[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1600[/TD]
[TD]1500[/TD]
[TD]1400[/TD]
[TD]1300[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]July 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1700[/TD]
[TD]1600[/TD]
[TD]1500[/TD]
[TD]1400[/TD]
[/TR]
[TR]
[TD]Aug 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1800[/TD]
[TD]1700[/TD]
[TD]1600[/TD]
[/TR]
[TR]
[TD]Sept 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1900[/TD]
[TD]1800[/TD]
[/TR]
[TR]
[TD]Oct 2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2000[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 248"]
<tbody>[TR]
[TD]Month
[/TD]
[TD]Date
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD="align: right"]1/1/2016
[/TD]
[TD="align: right"]187
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD="align: right"]1/2/2016
[/TD]
[TD="align: right"]387
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD="align: right"]1/5/2016
[/TD]
[TD="align: right"]77
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD="align: right"]1/16/2016
[/TD]
[TD="align: right"]487
[/TD]
[/TR]
[TR]
[TD]January
[/TD]
[TD="align: right"]1/17/2016
[/TD]
[TD="align: right"]395
[/TD]
[/TR]
[TR]
[TD]October
[/TD]
[TD="align: right"]10/12/2016
[/TD]
[TD="align: right"]195
[/TD]
[/TR]
[TR]
[TD]October
[/TD]
[TD="align: right"]10/15/2016
[/TD]
[TD="align: right"]362
[/TD]
[/TR]
[TR]
[TD]October
[/TD]
[TD="align: right"]10/22/2016
[/TD]
[TD="align: right"]412
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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