Need to sum groups of 7 columns without manual formula creation

Copper Contributor

I have a very long data set (each column is one day's worth of data), and I need to set up formulas to automatically provide a sum for that data, at the weekly and monthly level.

 

After creating 52 manual formulas for the weekly data in 2018, I realized that there must be an easier way.  Copying the formulas for all 52 weeks (in other words, 52 cells in a row, containing each week's formula) doesn't work, as that doesn't correspond to the right cells in the 2019 daily column set.

 

This is probably something basic - so no judgments, please!!  Any tips would be greatly appreciated.

4 Replies
What formulas did you use? If you can attach your sample file, please do.

@Twifoo, here's an excerpt, showing the formulas for a few selected weeks.

 

 CDEFGH
       
14Week #201845=D14+1=E14+1=F14+1=G14+1
15Product Category 1 Sales=SUM(MG372:MM372)=SUM(MN372:MT372)=SUM(MU372:NA372)=SUM(NB372:NH372)=SUM(NI372:NO372)
16 Product Category 2 Sales=SUM(MG373:MM373)=SUM(MN373:MT373)=SUM(MU373:NA373)=SUM(NB373:NH373)=SUM(NI373:NO373)
17Product Category 3 Sales=SUM(MG374:MM374)=SUM(MN374:MT374)=SUM(MU374:NA374)=SUM(NB374:NH374)=SUM(NI374:NO374)
Under what columns are your first day, last day, and first sum data?
Perhaps, your formula for week 1 will suffice to help me decipher my suggestion.