Home

Need to sum groups of 7 columns without manual formula creation

%3CLINGO-SUB%20id%3D%22lingo-sub-912600%22%20slang%3D%22en-US%22%3ENeed%20to%20sum%20groups%20of%207%20columns%20without%20manual%20formula%20creation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912600%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20very%20long%20data%20set%20(each%20column%20is%20one%20day's%20worth%20of%20data)%2C%20and%20I%20need%20to%20set%20up%20formulas%20to%20automatically%20provide%20a%20sum%20for%20that%20data%2C%20at%20the%20weekly%20and%20monthly%20level.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20creating%2052%20manual%20formulas%20for%20the%20weekly%20data%20in%202018%2C%20I%20realized%20that%20there%20must%20be%20an%20easier%20way.%26nbsp%3B%20Copying%20the%20formulas%20for%20all%2052%20weeks%20(in%20other%20words%2C%2052%20cells%20in%20a%20row%2C%20containing%20each%20week's%20formula)%20doesn't%20work%2C%20as%20that%20doesn't%20correspond%20to%20the%20right%20cells%20in%20the%202019%20daily%20column%20set.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20probably%20something%20basic%20-%20so%20no%20judgments%2C%20please!!%26nbsp%3B%20Any%20tips%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-912600%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-912621%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20sum%20groups%20of%207%20columns%20without%20manual%20formula%20creation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912621%22%20slang%3D%22en-US%22%3EWhat%20formulas%20did%20you%20use%3F%20If%20you%20can%20attach%20your%20sample%20file%2C%20please%20do.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-912672%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20sum%20groups%20of%207%20columns%20without%20manual%20formula%20creation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912672%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%2C%26nbsp%3Bhere's%20an%20excerpt%2C%20showing%20the%20formulas%20for%20a%20few%20selected%20weeks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EF%3C%2FTD%3E%3CTD%3EG%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3EWeek%20%23%3C%2FTD%3E%3CTD%3E201845%3C%2FTD%3E%3CTD%3E%3DD14%2B1%3C%2FTD%3E%3CTD%3E%3DE14%2B1%3C%2FTD%3E%3CTD%3E%3DF14%2B1%3C%2FTD%3E%3CTD%3E%3DG14%2B1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3EProduct%20Category%201%20Sales%3C%2FTD%3E%3CTD%3E%3DSUM(MG372%3AMM372)%3C%2FTD%3E%3CTD%3E%3DSUM(MN372%3AMT372)%3C%2FTD%3E%3CTD%3E%3DSUM(MU372%3ANA372)%3C%2FTD%3E%3CTD%3E%3DSUM(NB372%3ANH372)%3C%2FTD%3E%3CTD%3E%3DSUM(NI372%3ANO372)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3E%26nbsp%3BProduct%20Category%202%20Sales%3C%2FTD%3E%3CTD%3E%3DSUM(MG373%3AMM373)%3C%2FTD%3E%3CTD%3E%3DSUM(MN373%3AMT373)%3C%2FTD%3E%3CTD%3E%3DSUM(MU373%3ANA373)%3C%2FTD%3E%3CTD%3E%3DSUM(NB373%3ANH373)%3C%2FTD%3E%3CTD%3E%3DSUM(NI373%3ANO373)%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17%3C%2FTD%3E%3CTD%3EProduct%20Category%203%20Sales%3C%2FTD%3E%3CTD%3E%3DSUM(MG374%3AMM374)%3C%2FTD%3E%3CTD%3E%3DSUM(MN374%3AMT374)%3C%2FTD%3E%3CTD%3E%3DSUM(MU374%3ANA374)%3C%2FTD%3E%3CTD%3E%3DSUM(NB374%3ANH374)%3C%2FTD%3E%3CTD%3E%3DSUM(NI374%3ANO374)%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-912694%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20sum%20groups%20of%207%20columns%20without%20manual%20formula%20creation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912694%22%20slang%3D%22en-US%22%3EUnder%20what%20columns%20are%20your%20first%20day%2C%20last%20day%2C%20and%20first%20sum%20data%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-912703%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20sum%20groups%20of%207%20columns%20without%20manual%20formula%20creation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-912703%22%20slang%3D%22en-US%22%3EPerhaps%2C%20your%20formula%20for%20week%201%20will%20suffice%20to%20help%20me%20decipher%20my%20suggestion.%3C%2FLINGO-BODY%3E
winetaster99
New 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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies