I’m trying to create a tab in google sheets that acts like a bank register with multiple accounts. It would have a sender ID column, a receiver ID column and a transaction amount column. What I’m now trying to build is a column which outputs a balance for the sender at the point the transaction is requested to serve the purpose of validating whether they have enough funds in their account.
In order to do this I am attempting to create a cumulative cash in and cumulative cash out column that corresponds to the sender attempting to make the transaction which can then be used to calculate the balance for the sender.
I’m having trouble finding a formula that works for this purpose. It needs to say "sum if receiver ID matches this column’s sender ID AND if the transaction occurred prior to this one (is above this one in the sheet). It also needs to be an array formula to enable it to update automatically when new transactions are attempted to be made.
So far, the formulas I have tried are:
This uses a sequentially numbered column (1,2,3,4 etc.) and the '"<"& function to say the column must have a number less than the current number in the numbered column (hence, be above it). This however doesn’t seem to work, returning only 0s.
This uses the same sequentially numbered column but instead uses an OFFSET function to define the a range that should grow downwards as the rows argument in the offset refers to the sequentially numbers column. This only returns the initially specified range and does not grow throughout the array formula
Does anyone have any suggestions? I’ve been struggling with this for nearly a week and I think YouTube has reached its limit.