2

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.

Example

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.

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;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.