ArrayFormula with sum of previous rows

Question

I have an ArrayFormula to calculate a value for each row, and for each 6th row I want it to calculate the sum of the previous 5 instead.

Example sheet: https://docs.google.com/spreadsheets/d/18g2bOOBqsUgmy3ZXINOl6hcaMXf-uYJv7PGft247FjU/edit?usp=sharing

I have tried several routes, including google script, but keep banging my head against limitations of ArrayFormula.

What your mean? Value of each row in previous 5 instead is number of row or any other value — Theza
Row number is just an example. So five rows with calculated values and then a sum row yes — Ove Halseth

Theza Theza · Accepted Answer · 2020-03-05T08:03:34

You need make group by rows

My E.g

Cell A2 (Name groups):

=ArrayFormula(IF(B2:B<>"",FLOOR((ROW(A2:A)-2)/5)+1,""))

Column B (Your Data)

Cell E2 (Result):

=QUERY({QUERY({A2:B},"select Col1,sum(Col2) where Col1>0 group by Col1"); QUERY({A2:B},"select Col1,Col2 where Col1>0")}, "select Col2 where Col1>0 order by Col1,Col2 label Col2 ''")

Function References

Query

ArrayFormula with sum of previous rows

1 Answers