Getting simple formula to sum columns A-X where the columns summed depends on value in another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1130954%22%20slang%3D%22en-US%22%3EGetting%20simple%20formula%20to%20sum%20columns%20A-X%20where%20the%20columns%20summed%20depends%20on%20value%20in%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1130954%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20simple%20formula%20in%20cells%20H9%20and%20H11%20sums%20the%20values%20of%20the%20weeks%20to%20the%20right.%20Currently%20as%20the%20weeks%20progress%20I%20am%20updating%20the%20range%20manually%20.%20eg%20on%20week%204%20telling%20it%20to%20sum%20from%20range%20I9-L9%20.%20However%20what%20i%20want%20it%20todo%20is%20sum%20the%20right%20columns%20depending%20on%20the%20week%20number%20in%20another%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20this%20week%20(week%205%20)%20is%20knows%20to%20sum%20I9-M9%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eoriginally%20I%20was%20just%20using%20sum%20I-BH%20and%20capturing%20the%20whole%20year%2C%20however%20people%20want%20to%20pre-enter%20values%20in%20future%20weeks%20(as%20forecast)%20and%20so%20I%20want%20to%20avoid%20counting%20them%20unless%20in%20THAT%20week%20as%20it%20messes%20up%20the%20statistics%20from%20the%20tables%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F167474i654D3C98195C15F6%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1130954%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1131602%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20simple%20formula%20to%20sum%20columns%20A-X%20where%20the%20columns%20summed%20depends%20on%20value%20in%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1131602%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F534282%22%20target%3D%22_blank%22%3E%40shotgun-68%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1130978%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20simple%20formula%20to%20sum%20columns%20A-X%20where%20the%20columns%20summed%20depends%20on%20value%20in%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1130978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20was%20perfect%2C%20thank%20you%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1130962%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20simple%20formula%20to%20sum%20columns%20A-X%20where%20the%20columns%20summed%20depends%20on%20value%20in%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1130962%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F534282%22%20target%3D%22_blank%22%3E%40shotgun-68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20H9%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUM(I9%3AINDEX(I9%3ABH9%2CISOWEEKNUM(TODAY())))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

This simple formula in cells H9 and H11 sums the values of the weeks to the right. Currently as the weeks progress I am updating the range manually . eg on week 4 telling it to sum from range I9-L9 . However what i want it todo is sum the right columns depending on the week number in another cell.

 

So for this week (week 5 ) is knows to sum I9-M9 

 

originally I was just using sum I-BH and capturing the whole year, however people want to pre-enter values in future weeks (as forecast) and so I want to avoid counting them unless in THAT week as it messes up the statistics from the tables 

 

 

 

clipboard_image_0.png

3 Replies
Highlighted

@shotgun-68 

For H9 that could be

=SUM(I9:INDEX(I9:BH9,ISOWEEKNUM(TODAY())))

 

Highlighted
Highlighted