# Running Total In Google Sheets with Array

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

• This sheet was accidentally made to be publically editable -- I've changed it to read only and made an attempt to restore the sheet back to its original state. Sorry for any confusion. – Xzila Jul 10 '19 at 13:23

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]) {
sum = 0;
for (var j = 0; j < n; j++) {
if (names[j] == names[i] && dates[j] <= dates[i]) {
sum = sum + amounts[j];
}
}
}
else {
sum = '';
}
totals.push([sum]);
}
``````=ARRAYFORMULA(SUMIF(A2:A,"="&A2:A,C2:C))
• This formula is not desired because all results are just totals by the Name Column, and do not take into account the dates. The best result so far was given by user79865 ( although it doesn't use arrays ) `sumifs(C:C, A:A, A2, B:B, "<="&B2)` <-- this formula must be "copied down" such that it exists on every row being operated upon. – Xzila Mar 13 '17 at 21:59