Running Total In Google Sheets with Array

Question

QUESTION:

How do I create a running balance that has the following criteria:

1. It uses an array formula
2. the rows don't need to be sorted by date, but running total only returns balance of:
   • Rows with the same or earlier dates
   • Rows with the same exact account name
3. Balance accrues by Date, by Name. (i.e. it filters all results by one name and only adds AMT's whose date is the same or comes before)

CONTEXT:

I've created a check ledger, that accounts for deposits and withdrawals. here is a link to what I've been working on so far ( it has more columns than the picture below: because I've experimented with several solutions. )

WHAT I'VE TRIED: see google sheet

• =ARRAYFORMULA(SUMIF($A$2:A,$A$2:A,$C$2:C)) This only gives the current total ( not the running total for each account & it completely ignores the date column.
• Using an Array =SORT(A2:C,2,TRUE) to populate duplicate columns of A through C except now they are sorted. Then adding the formula =SUMIF($H$2:H2,H2,$J$2:J) Where column H is the sorted column of A & Column J is the sorted column C. Then copying that formula down to the rest of the column. Problem is, that it is not an Array. Although the result is closest to what I'm hoping to accomplish.

UPDATED 7/10/19:

The sheet I "shared" publically originally had edit access to everything on it was changed. I did my best to repair and improve it for ease of understanding (i.e all $ amounts were changed to 1, Bold & Purple highlights areas where formulas exist)

Answer 1

If you did not insist on array-based formula, then

sumifs(C:C, A:A, A2, B:B, "<="&B2)

entered in D2 and copied around the D column, would do the job.

I don't think that an arrayformula-based solution exists, because arrayformula runs through an array once, performing some element-wise operations. You want to compare each element with all others, which is one step further in complexity (a double for loop).

But you can use a custom function, something like =runningtotal(A2:A, B2:B, C2:C) which processes the columns and returns an array of running totals. The script implementing this function is below; add it via Tools > Script Editor. The logic is quite straightforward: as said above, it's a double for loop.

function runningTotal(names, dates, amounts) {
  var sum, totals = [], n = names.length;
  if (dates.length != n || amounts.length != n) {
    return 'Error: need three columns of equal length'; 
  }
  for (var i = 0; i < n; i++) {
    if (names[i][0]) {
      sum = 0;
      for (var j = 0; j < n; j++) {
        if (names[j][0] == names[i][0] && dates[j][0] <= dates[i][0]) {
          sum = sum + amounts[j][0];
        }
      }
    }
    else {
      sum = '';
    }
    totals.push([sum]);
  }
  return totals;
}

Answer 2

You can use an ARRAYFORMULA, combined with SUMIF:

=ARRAYFORMULA(SUMIF(A2:A,"="&A2:A,C2:C))