Multiple criteria sumif array formula with logic

Hi everyone,

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.

Thanks!

Hi Ed,

This would take a bit of time for me to work out the columns needed for this, because I think there are additional requirements for each transaction.

I imagine it like this, with columns of:

  • Sender ID
  • Receiver ID
  • Sender balance before transaction
  • Verify if transaction can happen

If transaction can not happen, there must be a further logic that does not count the row in question for later calculations.

I will try to work it out.

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:

ezgif-1-d7ce61d2e30c

Link to the spreadsheet: https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1848555900

3 Likes

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.

Any ideas where I’m going wrong?

Link to the copied spreadsheet: https://docs.google.com/spreadsheets/d/1MYDEB8-7F0SUCO6l7OIjYYjrC60DbeNKoOlFdFLmU4k/edit?usp=sharing

Hi Ed,

Please allow me the access to edit the file. I need to see the script and test it.

Thank you!

Done! Thanks :slight_smile:

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.

3 Likes

Simply brilliant.

1 Like

Here are some notes.

  • Accessing the scripts:

Navigate to Tools > Script Editor.

image

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.

image

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.

image

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:

image

After:

image

I have made all the necessary changes. Have another try and tell me if it works.

2 Likes

Fantastic, it works!

I’ll try to integrate this now into my app taking into account your very helpful notes.

Thank you so much Thinh!!

2 Likes

Thank you Jeff! I’ll take a look :slight_smile:

1 Like

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.

I’ve illustrated this problem in the same example document. Here is the link again: https://docs.google.com/spreadsheets/d/1MYDEB8-7F0SUCO6l7OIjYYjrC60DbeNKoOlFdFLmU4k/edit?usp=sharing

I’ve recorded the issue below as well

Is there more to this 500 row thing or is it a formula issue???

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.

1 Like

Hi Ed, I have altered the script and the sheet to make it work.

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.

Here’s a video.

ezgif-1-02792e4297b7

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):

Navigate to File > Spreadsheet Settings. What is your current setting for Calculation > Iterative Calculation?

Iterative calculations are on, recalculations = on change and every hours, max number of iterations = 20 (also tried 200). threshold = 0.05

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?