Dynamic rolling sum in excel

Question

How can I calculate the rolling sum of a column based on another column?

Example:

Input data are the first 3 columns in the below table. The result column that I am trying to get is highlighted in green. I want to get the dynamic sum of 'Daily sales' based on the corresponding 'No of days' given.

Can you explain the sum in the lower rows of your sample? Is there sample data missing? — JvdV, Jul 06 '23 at 07:05
@JvdV, yes the sample data are missing in the lower rows. Sorry about not mentioning that. — VanodyaPerera, Aug 04 '23 at 07:42

score 2 · Answer 1 · answered Jul 06 '23 at 07:02

Assuming there is sample data missing, hence the lower rows will yield different results, here is yet another alternative:

Formula in D1:

=MAP(B1:B12,C1:C12,LAMBDA(x,y,SUM(x:INDEX(B:B,ROW(x)+y-1))))

Or just don't use LAMBDA() at all, but a simple SUMIFS():

=SUMIFS(B1:B12,A1:A12,">="&A1:A12,A1:A12,"<="&A1:A12+C1:C12-1)

score 1 · Accepted Answer · answered Jul 06 '23 at 06:48

1

This formula worked. Add the 'OFFSET' function inside the 'SUM' function to input the dynamic array.

answered Jul 06 '23 at 06:48

VanodyaPerera

137
1
1
10

score 1 · Answer 3 · answered Jul 06 '23 at 06:57

1

If you are on Microsoft-365 then could try the following formula for dynamic spill results.

=MAP(C2:C13,LAMBDA(x,SUM(OFFSET(x,0,-1,x))))

answered Jul 06 '23 at 06:57

Harun24hr

30,391
4
21
36

Dynamic rolling sum in excel

3 Answers3