Using ARRAYFORMULA to Calculate Running Total of Payables (Alternative to INDIRECT)

Question

I use a Google Spreadsheet to keep track of the accounts payable per vendor. There is a sheet per vendor in the Spreadsheet. A simplified sheet looks like this:

When I receive a new invoice, an entry for the amount is made in the Credit column and when I release a payment, an entry for the amount is made in the Debit column. I keep track of the running total in the AC Payable column. I achieve this by using a formula in each cell of the AC Payable column (the example below is from cell E4):

=IF(
  ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),
  INDIRECT(ADDRESS(ROW()-1,COLUMN()))+C4-D4,
  C4-D4
)

The logic is simple. The running total for row n is calculated by:

AC Payable(n - 1) + Credit(n) - Debit(n)

This setup works fine, except I have to drag the formula into newly added rows. Is there a way to achieve this by using ARRAYFORMULA?

PS: I have found a solution using:

= ARRAYFORMULA(
    SUMIF(
      ROW(C3:C),
      "<="&ROW(C3:C),
      C3:C) 
    - 
    SUMIF(
      ROW(D3:D),
      "<="&ROW(D3:D),
      D3:D
    )
  )

I feel this is a suboptimal (The original sheet dates back to 2018. It has a lot of rows) solution since, in every row, it calculates the total of the Debit and Credit columns up to the current row and then subtracts the total of the Debit column from the total of the Credit column.

I am expecting a solution that would take advantage of the running total available in the previous row and not redo the whole calculation per row.

share a copy / sample of your sheet with an example of the desired output — player0, May 25 '22 at 09:15
You said, "I am expecting a solution that would take advantage of the running total available in the previous row and not redo the whole calculation per row." That is not possible with an array formula approach. You would wind up with a circular reference error if you tried. Both SUMIF and MMULT are quite fast even with large data sets if used correctly. Did you *try* the formula you "feel is suboptimal" and, if so, did you experience lag? — Erik Tyler, May 25 '22 at 18:33
@ErikTyler Yes, compared to the formula I posted, the one using `INDIRECT` to refer to the previous row's `AC Payable`, the `SUMIF` solution is slow enough for me to feel the difference between the performance of the two approaches. — Code Poet, Jun 01 '22 at 06:25
I think rather than abusing matrix multiplication, the ideal solution here is a truly iterative function that does exactly what you want - defined in a custom function https://developers.google.com/apps-script/guides/sheets/functions — Douglas Held, Jun 24 '22 at 21:22

player0 · Accepted Answer · 2022-05-27T10:37:16.917

solution for up to 1581 rows:

=ARRAYFORMULA(QUERY(QUERY(MMULT(TRANSPOSE((SEQUENCE(COUNTA(A3:A)*2)<=
 SEQUENCE(1, COUNTA(A3:A)*2))*FLATTEN(INDIRECT("C3:D"&COUNTA(A3:A)+ROW(A3)-1)*{1, -1})), 
 SEQUENCE(COUNTA(A3:A)*2, 1, 1, 0)), "offset 1", ), "skipping 2", ))

skills:

it's fast
it's smart
gets slower more rows you add
dies after 1581 rows

it's based on standard MMULT Running/Cumulative Total/Sum formula:

=ARRAYFORMULA(MMULT(TRANSPOSE((ROW(B1:B6) 
 <=TRANSPOSE(ROW(B1:B6)))*B1:B6), SIGN(B1:B6)))

but with a modification twist, because you got 2 columns to total

instead of ROW(B1:B6) we use a sequence of count of real data multiplied by two (because you got 2 columns):

SEQUENCE(COUNTA(A3:A)*2)

instead of TRANSPOSE(ROW(B1:B6)) we use again:

SEQUENCE(1, COUNTA(A3:A)*2)

combination of these pieces:

=ARRAYFORMULA(TRANSPOSE((SEQUENCE(COUNTA(A3:A)*2)<=SEQUENCE(1, COUNTA(A3:A)*2))))

will produce a matrix like:

and that's the reason why it dies with lots of rows because while you may think that if you have only 1500 rows in two columns, then formula will work only on 1500*2=3000 virtual cells, but in fact the MMULT formula processes (1500*2)*(1500*2)=9000000 virtual cells. still, it's worth to note, that this MMULT fx is great if deployed on a small scale.

next, instead of *B1:B6 we use:

*FLATTEN(INDIRECT("C3:D"&COUNTA(A3:A)+ROW(A3)-1)*{1, -1}))

eg. with INDIRECT we take only "valid" range of C3:D which is in your example sheet just C3:D5 and we multiply C column by 1 and D column by -1 to simulate subtraction and then we FLATTEN both columns into one single column. the part +ROW(A3)-1 is just an offset because you start from row 3

and the last part of standard RT fx - SIGN(B1:B6) is replaced with one column full of ones:

SEQUENCE(COUNTA(A3:A)*2, 1, 1, 0)

then we offset the output with inner QUERY by 1 because we are interested in a totals after subtraction and finally we use skipping 2 which means that we filter out every second value - again, we are interested in totals after subtraction of D column.

solution for more than 1581 rows:

=ARRAYFORMULA(
 SUMIF(SEQUENCE(COUNTA(A3:A)), "<="&SEQUENCE(COUNTA(A3:A)), INDIRECT("C3:C"&COUNTA(A3:A)))-
 SUMIF(SEQUENCE(COUNTA(A3:A)), "<="&SEQUENCE(COUNTA(A3:A)), INDIRECT("D3:D"&COUNTA(A3:A))))

skills:

supports more rows
looks less smart
sadly the third argument of SUMIF always needs to be a range
gets slower with more rows
it will get sick if you feed it with 10000 rows
it may kill off your sheet with 11000+ rows

Works like magic, but you make rocket science look easy. Care to briefly explain, because I'm lost? — Code Poet, May 25 '22 at 12:12
@CodePoet note that this will only work for about 3500 rows ish, right @Player0? — MattKing, May 25 '22 at 15:21
@MattKing actually its less than half of it because there are 2 columns — player0, May 25 '22 at 21:05
@player0 Thanks for taking the time to explain how your complex formula works. — Code Poet, May 27 '22 at 04:20

score 1 · Answer 2 · answered May 27 '22 at 00:45

1

Here'a modification of Ben Collins' running total formula

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   MMULT(TRANSPOSE((ROW(C2:C)<=TRANSPOSE(ROW(C2:C)))*C2:C),SIGN(C2:C))-
   MMULT(TRANSPOSE((ROW(D2:D)<=TRANSPOSE(ROW(D2:D)))*D2:D),SIGN(D2:D))))

answered May 27 '22 at 00:45

tomf

525
3
10

1

in theory, this will work up to 3162 rows as @MattKing previously mentioned. and also, both MMULT will in this case do pointless calculations for every empty row `((0+0+0+0+...)-(0+0+0+0+...))*1` – player0 May 27 '22 at 10:45

score 1 · Answer 3 · answered May 27 '22 at 12:03

yet another alternative to MMULT:

=INDEX(QUERY(FLATTEN(QUERY(QUERY(TRANSPOSE(QUERY(QUERY(TRANSPOSE(
 (SEQUENCE(COUNTA(A3:A)*2)<=SEQUENCE(1, COUNTA(A3:A)*2))*
 FLATTEN(INDIRECT("C3:D"&COUNTA(A3:A)+ROW(A3)-1)*{1, -1})), 
 "offset 1", ), "skipping 2", )), "select "&QUERY( 
 "sum(Col"&SEQUENCE(COUNTA(A3:A))&"),",, 9^9)&"' '"), 
 "offset 1", )), "where Col1 is not null", ))

but again, LTE (<=) limitation of 10M cells won't let you use more than 1581 rows in your case or 3162 rows in the standard cumulative sum case

(1581 rows * 2 columns) raised on 2nd power < 10 million cells

(1581*2)^2 = 9998244

Using ARRAYFORMULA to Calculate Running Total of Payables (Alternative to INDIRECT)

3 Answers3

solution for up to 1581 rows:

solution for more than 1581 rows:

Linked