# Dynamic Sum on rows

#### HCW1966

##### New Member
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
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
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)

Replies
11
Views
216
Replies
1
Views
344
Replies
4
Views
99
Replies
14
Views
243
Replies
1
Views
134

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.

### Which adblocker are you using?

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

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