Search code examples
google-sheetsgoogle-sheets-formulaarray-formulassumifscumulative-sum

Get new array from old array so nth cell represents the sum of all cells in old array up to its index n

I'm trying to get a one-dimensional new array from an old one, where the nth cell in the new array is the value of the sum of all values up to the nth cell in the old array. In the array that I already have the data in each cell represents the profits per day. And I would like to get a new array where each cell represents the accumulated net profit up to that day.

Some things I tried to do to come up with a solution:​​​​ https://docs.google.com/spreadsheets/d/1qpBFtW6r8oQF75htpLQDhL8IxejuM7vmKN8qHOn-5vY/edit?usp=sharing

  1. =ArrayFormula(SUM( OFFSET($A$2,0,0,ROW(A2:A)-1,1)))

  2. =A3:A+INDIRECT("B"&TO_TEXT(ROW()-1))

For the first formula needs to be applied to every single cell. Instead, I just want a formula for B2 that returns an array to generates all the values underneath it. And the second formula makes google sheets too slow to use for me.

Any help would be appreciated.

Solution 
  • =ARRAYFORMULA(IF(LEN(A2:A), (SUMIF(ROW(A2:A), "<="&ROW(A2:A), A2:A)), ))

    0