Array formula with running total that restarts based on values in another column

Question

I have tried to create a running total with some kind of filter as it were and have been unable to figure out how to do it. I have seen people do a running total using MMULT but not sure how to adapt it.

As you can see in column A there is every time specified if it is a new running total or if it continues and then C need to do a running total on column B until A has "New" in it again.

Google Sheets example.

Answer 1

It's easier to write a custom function for this than to look for a MMULT solution that may not even exist. The function would be used as

=runningtotal(B2:B12, A2:A12, "New")

where B2:B12 is the range with data, A2:A12 is the range with conditions for restart, and "New" is the value that tells that the total should restart.

The script runs a loop over the data, aggregates the total in total, and restarts when needed.

function runningTotal(data, conditions, str) {
  var output = [];
  var total = 0;
  for (var i in data) {
    total = (conditions[i][0] == str ? data[i][0] : total + data[i][0]);
    output.push([total]);
  }
  return output;
}