Sort running sum in descending order using excel formula

[garyj]

Hello again..

I have a new accounting project I've been working on. I have a general journal (Table17) which lists all transactions. Then I use dynamic commands such as filter and choosecols to create account totals, such as Accounts Payable, Bank Cash, etc. It's pretty slick.

Below is the filter command that brings the data needed to the Banks/Cash record.
* BCSelectAcct is a data validation list which selects a particular bank account or petty cash etc.
* Column 2 of the GJ is the Date column - which becomes the sort column (1) for the filtered data.
* Column 4 is Item, Columns 7 and 8 are Debit and Credit respectively.


=IFERROR(CHOOSECOLS(SORT(FILTER(Table17,(Table17[Account]=BCSelectAcct)),,1),2,4,7,8),"None found")

In order to create a running sum column I made a hidden column, and then a running total column. The forumlas are shown below as follows and they spill because they follow the main filter data in A4, and then in E4.



=IFERROR(CHOOSECOLS(A4#,3)-CHOOSECOLS(A4#,4),"")
=SUBTOTAL(9,OFFSET(E4,0,0,SEQUENCE(ROWS(E4#)),1))


Now the problem. After about 100 or so entries, I realize that it needs to be sorted in the opposite order. Newest date at top.
The sort is easy enough, just by adding a negative sign to the 1 after the two consecutive commas in the first formula.

BUT I am lost on the running total. I tried versions of subtotal and also simplified it to =F5+E4#, which works in theory but creates a circular formula within the spill as soon as it goes to the second row. I can't find the problem on internet searches, but I can't believe it hasn't been asked before. Any advice?

Gary

[p45cal]

=SUBTOTAL(9,OFFSET(E4,SEQUENCE(ROWS(E4#),,0),0,SEQUENCE(ROWS(E4#),,ROWS(E4# ),-1)))?
…but I'm sure there's an easier way; I'll have a think.
edit:
maybe:
=SUMIF(INDEX(A4#,0,1),"<=" & INDEX(A4#,0,1),E4#)and it doesn't matter which way things are sorted, but if there are repeating dates, the same dates will have the same running totals.
Here, I've guessed that column 1 of A4# contains dates. It would save us guessing (wrongly) if you attached a representative workbok.

[garyj]

Yes, that works, p45cal. Awesome and thanks.

For review, yes the second one works, but duplicates values for repeating dates. The first formula works line by line from the bottom regardless of repeating dates.
Yes, on the first column being a date field, which I supposed would be extrapolated from this comment above. "Column 2 of the GJ is the Date column - which becomes the sort column (1) for the filtered data." After think this through more, I realize I could have used the A4# value instead of column (1)... because it brings more clarity. I didn't have time to figure out how to send a worksheet this morning, as there was a lot of personal data already entered and I would have had to create a new sheet with random data.

Anyway, thanks again.