Can't find anything for this, and my brain keeps going to a object oriented solutions which as far as i know cant be implemented here.

I have a google sheet with the following data, i want to figure out how to get the sum of column b values, but only for those values after the last known negative number in column a.

IE: the formula should give me a sum of 11 and 13 = 24 since only 4 and 5 are the positives number after the last known negative number.

CodePudding user response：

Try this:

=ARRAYFORMULA(SUM(INDIRECT("B"&MATCH(LOOKUP(9^9,IF(A1:A<0,A1:A)),A1:A,0) 1&":B")))

Looking at this formula piecemeal:

1. Lookup for the last negative number in the range: LOOKUP(9^9,IF(A1:A<0,A1:A))

2. Find its position in the range and add 1 to get next position: MATCH(<formula in step 1>),A1:A,0) 1

3. Build the sum range using indirect. SUM(INDIRECT("B"&<formula in step 2>&":B"))

4. Encapsulate in ARRAYFORMULA to retrieve the result.

Example:

CodePudding user response：

I was able to solve this with Apps Scripts making a custom function.

The function is very basic.

function sumFromLastNeg(data) {
  var run_sum = 0;
  for (var i = 0; i < data.length; i  ) {
    if (data[i][0] >= 0){
      run_sum  = data[i][1];
    }
    else if (data[i][0] < 0){
      run_sum = 0;
    }
  }
  return run_sum;
}

The functions is called like so =sumFromLastNeg(A1:B)

My original thinking was i would not be able to do this, but through my looking for solutions found scripting in google sheets.

Hope this helps people!