Spreadsheet: 735771d1623166396-dynamic-sum-of-multiple-named-tables-structured-referencing-dynamic-sum-multiple-named-tables
Sheet: 1D Sum Last Rows

Total balance, ALL checking accounts → |  |  |  |  |  |  | [NAME] STATIC FORMULA WON'T ALLOW DYNAMIC INCLUSION OF NEW       STATEMENTS TABLES TO THE RIGHT OF THE 3 EXISTING ONES!!!
Bank of America |  |  |  |  | JPMorgan Chase |  |  |  |  | US Bank
Date | Transaction | Amount | Balance |  | Date | Transaction | Amount | Balance |  | Date | Transaction | Amount | Balance |  | LOOKUP and INDIRECT |  |  | SUBTOTAL9 | HYPOTHETICAL FULL "SPILL" FORMULA THAT WON'T WORK
2016-10-20 00:00:00 | Beginning balance | 0 |  |  | 2020-03-12 00:00:00 |  | 0 |  |  | 2021-05-02 00:00:00 |  | 0 |  |  |  |  |  | (should spill, if source OK)
2016-10-21 00:00:00 | Deposit | 10000 |  |  | 2020-03-12 00:00:00 | Deposit | 330712.94 |  |  | 2021-05-03 00:00:00 | Deposit | 6.72 |  |  | (DOES NOT SPILL!)
2016-11-01 00:00:00 | Wire transfer out | -5300 |  |  | 2020-03-16 00:00:00 | Investment instrument | -30000 |  |  | SEQUENCE/ COUNT stmts | Columns text for INDIRECT |  |  |  | ↓ |  |  | ↓ | ↓
2016-11-01 00:00:00 | Folio 1 | -1550.64 |  |  | 2020-03-16 00:00:00 | Investment instrument | -50000 |  |  |  |  |  |  |  |  |  |  |  | =SUBTOTAL(9;LOOKUP(2;1/(INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")<>"");INDIRECT("Stmt"&SEQUENCE(COUNTA(SEQUENCE(SUBTOTAL(3;$3:$3))))&"["&Stmt1[[#Headers];[Balance]]&"]")))
2016-11-01 00:00:00 | Folio 2 | -129.69 |  |  | 2020-03-17 00:00:00 | Investment instrument | -50000 |  |  | (spills OK) | (spills OK) |  |  |  | APPROACH ONLY WORKS INDIVIDUALLY |  |  | NO "#" array, so NO "spill" formula
2016-11-03 00:00:00 | Folio 3 | -1774.75 |  |  | 2020-03-19 00:00:00 | Investment instrument | -50000 |  |  | ↓ | ↓ |  |  |  |  |  |  |  | Reasons:
2016-11-04 00:00:00 | Debit card | -174.96 |  |  | 2020-03-19 00:00:00 | Investment instrument | -50000
2016-11-08 00:00:00 | Debit card | -60.17 |  |  | 2020-04-01 00:00:00 | Investment instrument | -25000 |  |  | 2 | Stmt2[Balance] |  |  |  | ↓ |  |  | ↓
2016-11-09 00:00:00 | Debit card | -20.8 |  |  | 2020-04-24 00:00:00 | Wire transfer out | -25000 |  |  | 3 | Stmt3[Balance] |  |  |  |  |  |  |  | (cannot use SUM or AGGREGATE as alternatives since they also do NOT work with spilled ranges).
2016-11-10 00:00:00 | Debit card | -16.5 |  |  | 2020-06-23 00:00:00 | Deposit | 7000
2016-11-10 00:00:00 | Deposit | 2904.67 |  |  | 2020-06-27 00:00:00 | Deposit | 4000 |  |  |  | Stmt2[[#Headers];[Balance]]
2016-11-11 00:00:00 | Debit card | -36.3 |  |  | 2020-06-29 00:00:00 | Deposit | 4000 |  |  |  | Stmt3[[#Headers];[Balance]]
2016-11-14 00:00:00 | Debit card | -13.5 |  |  |  |  |  |  |  |  |  |  |  |  | INDEX/MAX/ROW and INDIRECT |  |  | SUBTOTAL9 | FULL "SPILL" FORMULA NO LONGER RETURNS AN ERROR, BUT STILL WON'T WORK
2016-11-16 00:00:00 | Folio 4 | -8158.67 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | (should spill, if source OK)
2016-11-16 00:00:00 | Deposit | 5000 |  |  |  |  |  |  |  |  |  |  |  |  | (Spills, but with ERROR!)
2016-11-22 00:00:00 | Folio 5 | -275.47 |  |  |  |  |  |  |  |  |  |  |  |  | ↓ |  |  | ↓ | ↓
2016-11-22 00:00:00 | Deposit | 2904.67
2016-11-22 00:00:00 | Debit card | -31.59 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ↓
2016-11-28 00:00:00 | Debit card | -25.6
2016-11-29 00:00:00 | Cash withdrawal | -200 |  |  |  |  |  |  |  |  |  |  |  |  | APPROACH ONLY WORKS INDIVIDUALLY |  |  | NO "#" array, so NO "spill" formula
2016-11-30 00:00:00 | [NAME] | 8600 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Reason:
2016-11-30 00:00:00 | Debit card | -14.9
2016-12-01 00:00:00 | Debit card | -20.5 |  |  |  |  |  |  |  |  |  |  |  |  | ↓ |  |  | ↓
2016-12-01 00:00:00 | Folio 6 | -95.48
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | INDEX/COUNTA (SUBTOTAL3) and INDIRECT |  |  | SUBTOTAL9 | FULL "SPILL" FORMULA NO LONGER RETURNS AN ERROR, BUT STILL WON'T WORK
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | (should spill, if source OK)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | (Spills, but with ERROR!)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ↓ |  |  | ↓ | ↓
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ↓
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | APPROACH ONLY WORKS INDIVIDUALLY (using COUNTA) |  |  | NO "#" array, so NO "spill" formula
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Reason:
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ↓ |  |  | ↓
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | INDIRECT and ADDRESS |  |  | SUBTOTAL9 | FULL "SPILL" FORMULA NO LONGER RETURNS AN ERROR, BUT STILL WON'T WORK
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | (should spill, if source OK)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | (Spills, but with ERROR!)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ↓ |  |  | ↓ | ↓
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ↓
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | APPROACH ONLY WORKS INDIVIDUALLY (using COUNTA) |  |  | NO "#" array, so NO "spill" formula
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ↓ |  |  | ↓