Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulassumifs

Get previous rowindex in Google Sheets where certain columnvalue is zero

Consider a sheet like: 

rowNr | Another Col | Filled    | Cumul. Size
0       2             -1000       -1000
1       3              1000        0
2       1             -5000       -5000
3       4              5000        0
4       5             -10000      -10000
5       2             -10000      -20000
6       1             -20000      -40000
6       4              40000       0

The 'Cumul. Size'-column displays the cumulative sum of the 'filled' column.

each time Cummulutive Size = 0, I need to calculate the sum of 'Another Column' for all previous rows until 'Cummulutive Size' != 0 again. For rows where 'Cummulutive Size' = 0, display '' (blank)

So something like this: 

rowNr | Another Col | Filled    | Cumul. Size | calculated
0       2             -1000       -1000  
1       3              1000        0            5
2       1             -5000       -5000 
3       4              5000        0            5
4       5             -10000      -10000 
5       2             -10000      -20000 
6       1             -20000      -40000 
6       4              40000       0            12

I'm sure I can create something working as long as I can find a function with a signature similar to: findPreviousRowIndex(curRowIndex, whereCondition)

Any pointers much appreciated

EDIT Link To example Google Sheet

Solution

  • paste in D2 cell and drag down:

    =ARRAYFORMULA(IF(LEN(A2), IF(C2=0, SUM(INDIRECT(ADDRESS(IFERROR(MAX(IF(
 INDIRECT("C1:C"&ROW()-1)=0, ROW(A:A), ))+1, 2), 1, 4)&":A"&ROW())), ), ))

    0