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.

Answer 1

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

Answer 2

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))))

Answer 3

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)