Dynamic Sum on rows

HCW1966

New Member
Joined
May 6, 2016
Messages
18
Hi there,
Please I am looking for some help on a dynamic sum formula.

I have week numbers 1 to 52 in rows. The current week is week 16, I want to be able to sum all the weeks from week 1 through to 16, and then when next week comes round wk17, I want the formulae to be dynamic and calculate wk1 through to 17 and so on as the weeks progress.
Any ideas would be greatly appreciated

thanks Harry
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,400
Office Version
  1. 365
Platform
  1. Windows
Use an expanding range. Like the formula here in E5, which you copy on down to the row of Week 52:
MrExcel posts18.xlsx
CDE
4weekvaluesum
511010
62818
73725
841035
95843
106144
117448
128149
139251
1410253
1511255
16121065
1713974
1814882
1915284
2016185
2117893
2218194
23198102
24208110
Sheet70
Cell Formulas
RangeFormula
C5:C56C5=SEQUENCE(52)
E5:E24E5=SUM(D$5:D5)
Dynamic array formulas.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

You can also do something like this.
Depending on how you derived your Week Numbers in Column A, adjust the WEEKNUM function's 2nd argument, "return type" to match:

Book3.xlsx
ABC
1weekvalueSum to Current Week #
211093
328
437
5410
658
761
874
981
1092
11102
12112
131210
14139
15148
16152
17161
18178
19181
20198
21208
Sheet924
Cell Formulas
RangeFormula
C2C2=SUMIF(A2:A54,"<="&WEEKNUM(TODAY()),B2:B54)
 

Forum statistics

Threads
1,141,122
Messages
5,704,431
Members
421,349
Latest member
Santhosh3188

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top