Arrayformula to calc sum for dynamic offset range

Question

I'm trying to calculate values with an arrayformula, based on the last 14 days (the last 14 rows, since every row is 1 day).
I want N110:N to have the values (in example: sum) from, let's say, I96:I110.
Means, the values in N110 should be sum(I96:I110). For N111 it should be sum(I97:I111) etc.

I have something like = ARRAYFORMULA("I"& Row(I110:I)-14 & ":I" & Row(I110:I)) which returns

I96:I110
I97:I111
I98:I112
...

in each row.

I cannot wrap this into the arrayformula, since Indirect() is not working here and is returning only the first value.
I also tried with offset, which led to the same result.

Basically I'm trying to use an arrayformula to calc values by a dynamic offset range with a fixed size (14).

I could solve it with google apps script, but I want to try with arrayformula.

Answer 1

Make sure that N110:N is empty, and then place the following formula in N110:

=ArrayFormula(IF(I110:I="",,SUMIF(ROW(I96:I),"<="&ROW(I110:I),I96:I)-SUMIF(ROW(I96:I),"<="&ROW(I110:I)-15,I96:I)))

This formula assumes that there are no blank cells interspersed between numbers in I96:I (though there may be blank cells after the number list runs out).

Essentially, this sums all cells in in the range up to the current row and then subtracts the total of all cells in rows prior to "15 cells back" as marked from the current cell.

Answer 2

try:

=INDEX(IFNA(VLOOKUP(ROW(A1:A), 
 QUERY(SPLIT(FLATTEN(ROW(A1:A)&"×"&ARRAY_CONSTRAIN(
 SPLIT(FLATTEN(QUERY(TRANSPOSE(IF(ROW(A1:A)<=TRANSPOSE(ROW(A1:A))=TRUE, 
 TRANSPOSE(A1:A)&"×", )),,9^9)), "×"), 9^9, 14 +N("14 day window"))), "×"),
 "select Col1,sum(Col2)
  where Col2 is not null
  group by Col1"), 2, )))