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

Question

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).

Answer 1

Try these custom functions

// mike steelson
function sumSinceLastFormula(rng){
  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
}
function countaSinceLastFormula(rng){
  var lastRow = SpreadsheetApp.getActiveRange().getRow()-1
  var col = SpreadsheetApp.getActiveRange().getColumn()
  var counta=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 {counta++}
  }
  return counta
}
function countifSinceLastFormula(rng,crit){
  var lastRow = SpreadsheetApp.getActiveRange().getRow()-1
  var col = SpreadsheetApp.getActiveRange().getColumn()
  var countif=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 {if (SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(i,col).getValue()==crit) {countif++} }
  }
  return countif
}

to automatically update the values, add reference to previous cells

=sumSinceLastFormula(F$2:F8)

when in F9, and copy where you need it.

https://docs.google.com/spreadsheets/d/1iXDbYDd_5rmHa1E41zobTWB6MKvABR1ERpCgcValIng/copy