How can I make a function in google sheet run on all the previous cells above until the previous fun-CodePudding

I want to make a function in google sheet, for example here "sum" I want it sum all above cells until the previous another function So if I copied it to another row it will sum all above cell until the previous function also (3 of pic).

CodePudding user response：

You can calculate the cumulative by a single formula like

={"sum";arrayformula(SUMIF(ROW(A2:A),"<="&ROW(A2:A),F2:F))}

CodePudding user response：

Try this custom function

function sumSinceLastFormula(){
  var lastRow = SpreadsheetApp.getActiveRange().getRow()-1
  var col = SpreadsheetApp.getActiveRange().getColumn()
  var sum=0
  for (var i = lastRow; i>1; i--){
    var value = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(i,col).getFormula()
    if (value && value.toString().charAt(0) === '=') {break}
    else {sum  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(i,col).getValue()}
  }
  return sum
}

and then put =sumSinceLastFormula() in the cell, assuming the numbers start in row # 2

edit

to automatically update the values, add reference to previous cells

=sumSinceLastFormula(F$1:F8)

when in F9, and copy where you need it. https://docs.google.com/spreadsheets/d/1iXDbYDd_5rmHa1E41zobTWB6MKvABR1ERpCgcValIng/copy