How to Efficiently Calculate Last Matching Value in google sheets with Complex Conditions?

Question

TL:DR; Make this code formula more efficient / elegant

    =IFERROR((IFS(OR(I46="UPI ICICI", I46="iMobile", I46="ICICI card"),IFERROR(INDEX(FILTER($J$5:J45, ($I$5:I45 = "UPI ICICI") + ($I$5:I45 = "iMobile") + ($I$5:I45 = "ICICI card")), COUNTA(FILTER($J$5:J45, ($I$5:I45 = "UPI ICICI") + ($I$5:I45 = "iMobile") + ($I$5:I45 = "ICICI card")))), $B$2),OR(I46="UPI SBI", I46="SBI card"),IFERROR(INDEX(FILTER($J$5:J45, ($I$5:I45 = "UPI SBI") + ($I$5:I45 = "SBI card")), COUNTA(FILTER($J$5:J45, ($I$5:I45 = "UPI SBI") + ($I$5:I45 = "SBI card")))), $D$2),OR(I46="Apay card", I46="Coral card"),IFERROR(INDEX(FILTER($J$5:J45, ($I$5:I45 = "Apay card") + ($I$5:I45 = "Coral card")), COUNTA(FILTER($J$5:J45, ($I$5:I45 = "Apay card") + ($I$5:I45 = "Coral card")))), $C$2),OR(I46="Cash"),IFERROR(INDEX(FILTER($J$5:J45, ($I$5:I45 = "Cash")), COUNTA(FILTER($J$5:J45, ($I$5:I45 = "Cash") ))), $E$2)))-C46,"uninitiated")

I have a google sheet where I track my personal finances. I have 4 main streams of money named ICICI Savings, ICICI Credit, SBI Savings, Cash. My usual modes of payment are UPI ICICI, iMobile, ICICI card {which correspond to ICICI Savings }, UPI SBI, SBI card {which correspond to SBI Savings }, Apay card, Coral card {which correspond to ICICI Credit } and Cash {well no points for guessing that this corresponds to Cash }

A snapshot of my sheet for better context:

Now, my problem was, whenever I make a transaction entry, {Amount is entered in the "C" column, thus in the formula you see above has "-C46"} I want the sheet to automatically update my totals, i.e., If I pay with cash in Row 46, it should search for the last corresponding value to cash, till above row 46, and subtract C46 from that. Similary, if I make a UPI ICICI transaction, it should take from the last value among "UPI ICICI, iMobile, ICICI card" and subtract C46 from that and so on. If there is no such corresponding value, it can take from the last month's carryover, which is in B2, C2, E2, D2 and then subtract C46 from that. If a new mode of payment is added, or it is empty, it will just output the string "uninitiated".

Hope that is clear.

Now the formula I am currently using is as below. Note: I am not using a google appscript code, because I don't know of a way that it refreshes without edit, and also will not work on mobile devices. =IFERROR((IFS(OR(I46="UPI ICICI", I46="iMobile", I46="ICICI card"),IFERROR(INDEX(FILTER($J$5:J45, ($I$5:I45 = "UPI ICICI") + ($I$5:I45 = "iMobile") + ($I$5:I45 = "ICICI card")), COUNTA(FILTER($J$5:J45, ($I$5:I45 = "UPI ICICI") + ($I$5:I45 = "iMobile") + ($I$5:I45 = "ICICI card")))), $B$2),OR(I46="UPI SBI", I46="SBI card"),IFERROR(INDEX(FILTER($J$5:J45, ($I$5:I45 = "UPI SBI") + ($I$5:I45 = "SBI card")), COUNTA(FILTER($J$5:J45, ($I$5:I45 = "UPI SBI") + ($I$5:I45 = "SBI card")))), $D$2),OR(I46="Apay card", I46="Coral card"),IFERROR(INDEX(FILTER($J$5:J45, ($I$5:I45 = "Apay card") + ($I$5:I45 = "Coral card")), COUNTA(FILTER($J$5:J45, ($I$5:I45 = "Apay card") + ($I$5:I45 = "Coral card")))), $C$2),OR(I46="Cash"),IFERROR(INDEX(FILTER($J$5:J45, ($I$5:I45 = "Cash")), COUNTA(FILTER($J$5:J45, ($I$5:I45 = "Cash") ))), $E$2)))-C46,"uninitiated")

Is there a more efficient, mostly looking for elegant formula to do the same. I did ask chatGPT, but I am unable to explain it to that well enough. Hope you guys can help me out!

Answer 1

This seems like a strange way to keep track of balances. Running totals in the same column for different meta-accounts seems more confusing than useful. I may be missing something.

Here are two formulas that will calculate:

1. Current Total by Account (aggregates the related modes of payment)
2. Aggregated running totals for each account listed beside relevant modes of payment.

• The payment modes and their account mappings are stored on a separate sheet named helper

• I added a category Unknown similar to your uninitiated. It makes sense to track them so your current balance always equals the sum of all payments plus the opening balances.

I added an EXAMPLE SPREADSHEET that you can play with. The spreadsheet is freely editable (anonymously) by anyone so I recommend you copy it to your own Google Drive in order to avoid issues introduced by other users.

Current Totals Formula

=LET(amt,C9:C, mode,I9:I, opn,B1:F2, hlp,helper!A2:B,
   act,ARRAYFORMULA(IFNA(VLOOKUP(mode,hlp,2,0), "Unknown")),
   ttl,MAP(INDEX(opn,1), LAMBDA(h, HLOOKUP(h,opn,2,0)+
     IFERROR(SUMPRODUCT(amt,act=h)))),
   {ttl, SUM(ttl)})

1. Uses LET to store intermediate calculations in named variables.

2. Input ranges are listed first:

   • amt: payment amount column
   • mode: payment mode column
   • opn: account names and opening balances
   • hlp: mappings mode to account names

3. act stores the mode values after converting them to their associated account name using HLOOKUP in an ARRAYFORMULA to find the associated account names in the range hlp. Where there is no match, IFNA provides the account named "Unknown".

4. An array for current balances is created and stored in ttl using MAP to pass the account names found in row 1 of opn, one by one, into a LAMBDA function that stores the current account name in h.

5. LAMBDA's formula adds the opening balance for h to the values in amt also associated with that same account h:

   1. HLOOKUP returns the opening balance from row 2 of opn where h matches the name in row 1
   2. SUMPRODUCT returns an array of amt where the account name matches h, and IFERROR returns nothing if there are no results.

6. Finally ttl (the one-row array containing each account's current total in a separate column) is returned followed by an additional column containing the combined total of all accounts SUM(ttl).

Running Totals Formula in J9

=LET(rng,data!A9:I, prev,A:F,  hlp,helper!A:B,
  rws,ARRAYFORMULA(IFNA(MATCH(2, 1/(LEN(C9:C))))),
  qr, QUERY(rng, "Select C, I  Limit "&rws&"",0),
  arr,{SEQUENCE(rws), INDEX(qr,,1), ARRAYFORMULA(IFNA(
    VLOOKUP(INDEX(qr,,2),hlp,2,0), "Unknown"))},
  str,MAP(UNIQUE(INDEX(arr,,3)), LAMBDA(u, 
    TOROW(LET(o,FILTER(arr, INDEX(arr,,3)=u), 
      ARRAYFORMULA(INDEX(o,,1)& "↔️"& INDEX(o,,2)& "↔️"& 
      SCAN(0+HLOOKUP(u,prev,2,0), INDEX(o,,2), LAMBDA(a,v, a+v))))))),
  CHOOSECOLS(SORT(BYROW(TOCOL(str,1), LAMBDA(s, SPLIT(s, "↔️")))),3))