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:
=ArrayFormula(SUMIF(D2:D&A2:A,B3:B&“<”&A3:A,O2:O))
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.
*=ARRAYFORMULA(SUMIF($D$2:OFFSET($D$1,A3,0),B3:B,$O$2:OFFSET($O$1,A3,0)))
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.
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.
That’s absolutely fantastic! Exactly what I need, thank you Thinh! I’ve never used scripts before though so I’ve encountered some problems replicating this.
I’ve created a test document to make sure I can copy the formulas and scripts across but this doesn’t seem to be working. There are error messages showing up on the ‘DataBetween.gs’ and ‘CopyDown.gs’ script files when trying to run them and column D’s formula does not copy down.
Have a look at the Currency Transfer/Purchase example in https://concepts.glideapp.io/. It’s old and could use some updating, but maybe it will give you some ideas.
This script copies down the formula in column “4” (which is D), you don’t need to modify anything other than the Sheet Name. It is “Bank account” here, but in your file it is “Sheet1”, so I have changed the name in the script to “Sheet1”, or you can do it the opposite way.
Enable triggers:
Navigate to Edit > Current project’s triggers inside the Script editor.
Press “Add trigger” button, choose the right function to run, set the event type to “On change”.
Mind the data type:
If you’re treating IDs as text, then the query part where you access the value from a cell will be written differently from when you treat IDs as number. Your copy sheet treats 1, 2, 3 and numbers, that’s why it did not work.
B = ‘"&A5&"’ when A5 is text
B = “&A5&” when A5 is number
I have changed all IDs to text, you can differentiate text and number by their alignment. Text aligns to the left, numbers align to the right.
Before:
After:
I have made all the necessary changes. Have another try and tell me if it works.
Hi Thinh. I’ve tried to integrate this into my app and all seems to work well apart from one issue:
When manually generating a new row through the ‘add new rows at bottom’ button it works great. The issue is that the data is coming in through a form on a second tab which is pushed through to sheet 1 through an array formula reference.
As a consequence each time a new entry is added to the form tab 500 new rows are generated and the column D formula is not copied down.
It’s the way Sheets has always behaved, as far as I know. When you have only 10 rows, for example, but new data is input into that sheet using an external source (whether forms, arrayformula/filter formulae, etc.) a lot of empty rows are added, in this case 500. I remembered it was 1000 with one of my old sheets.
The script now takes the range of values from column D, filter out null values, then determine the next empty row to copy the formula into using autoFillToNeighbour.
I have also removed the “bank” rows and add the initial 100 to the formula. Changes were made to the conditions as well to suit with the new structure.
Fantastic, thanks Thinh! I’ve now implemented this in my app and works well apart from one issue:
The ‘Transaction can happen?’ column with the TRUE/FALSE array formula seems to fluctuate, with the array filling down and then not filling down. (This is problematic as I need to create a second tab that only shows the validated transaction and uses TRUE/FALSE as the criteria for this filtering.)
I wonder if you or anyone else has encountered this arrayformula fluctuation before?
You can see the issue in this video (pay attention to column E):
Have no idea what’s the problem here to be honest, your video shows that there’s still calculation to be done before the arrayformula fills the column again. Is there a too complex system behind this so that the calculation takes too much time?
