Ok I’m back with what seems to work.
Firstly, ArrayFormula does not work with SUMIFS, or QUERY. It would have been such easier tasks had it been that way.
I have worked with many people who want to implement ARRAYFORMULA into their work so that automatic calculation can be applied whenever there’s a new row. Recently I faced the same problem when working on a logic with @Robert_Petitto and wrote a little script as a workaround, I will have a single post for this later so it can be used as a reference for future problems.
Ok so here’s the setup:
- Sender ID
- Receiver ID
- Amount send
- Sender balance (before transaction)
- Confirm if transaction can happen
Scripts, formula and settings:
- A script to automatically copy down the formula in column D when a new row is generated.
- 3 rows of assigning initial bank balance (100) to 3 IDs (marked as green).
- A QUERY formula to calculate the cumulative bank balance for ID in question by the logic: (Sum of all amount receive so far) - (Sum of all amount send so far), in cases where transactions marked as TRUE (can happen).
- File > Spreadsheet Settings > Turn on iterative calculation.
Demo:
Link to the spreadsheet: https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1848555900