Dynamic Sum on rows

[HCW1966]

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

 

[DRSteele]

Use an expanding range. Like the formula here in E5, which you copy on down to the row of Week 52:

MrExcel posts18.xlsx
	C	D	E
4	week	value	sum
5	1	10	10
6	2	8	18
7	3	7	25
8	4	10	35
9	5	8	43
10	6	1	44
11	7	4	48
12	8	1	49
13	9	2	51
14	10	2	53
15	11	2	55
16	12	10	65
17	13	9	74
18	14	8	82
19	15	2	84
20	16	1	85
21	17	8	93
22	18	1	94
23	19	8	102
24	20	8	110
Sheet70

Cell Formulas
Range	Formula
C5:C56	C5	=SEQUENCE(52)
E5:E24	E5	=SUM(D$5:D5)
Dynamic array formulas.

 

[jtakw]

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
	A	B	C
1	week	value	Sum to Current Week #
2	1	10	93
3	2	8
4	3	7
5	4	10
6	5	8
7	6	1
8	7	4
9	8	1
10	9	2
11	10	2
12	11	2
13	12	10
14	13	9
15	14	8
16	15	2
17	16	1
18	17	8
19	18	1
20	19	8
21	20	8
Sheet924

Cell Formulas
Range	Formula
C2	C2	=SUMIF(A2:A54,"<="&WEEKNUM(TODAY()),B2:B54)