CODEWITHSUNDEEP
excel-formula
dohanin
dohanin

Reputation: 11

Excel multiple running totals

I'm trying to make a simple formula for multiple running totals.

Sample:

Basically, it's for recording transactions for different accounts, showing the running total of that particular account for each row. So, it's impossible to use SCAN+LAMBDA function. One way to do it is to have a set of helper arrays somewhere, but here I'm using another way, by using XLOOKUP.

=C2+XLOOKUP(D2,D$1:OFFSET(D2,-1,),E$1:OFFSET(E2,-1,),0,,-1)

Basically, it looks up the last account balance above the current row of the corresponding account and add the current transaction amounnt to it. It works by draggin down to all the transaction rows.

Since the number of transactions is over 10 thousand, I was trying to minimize the file size by using named function with LAMBDA.

Name: AddtoBalance

=LAMBDA(c,c+XLOOKUP(OFFSET(c,,1),Sheet1!E$1:OFFSET(c,-1,1),Sheet1!F$1:OFFSET(c,-1,2),0,,-1))

And changed cell E2 to =AddtoBalance(C2)

and dragged it down to all transaction rows.

However, after saving and re-opening, the cells are having errors. I have to go to Name Manager, click Edit but without doing anything and Close it. Then the cells becomes fine again. It seems to me that when re-opening a workbook, the formulas are not calculated sequentially from top to bottom. Is that right? Is there any options to change it?

Error:

Upvotes: 0

Views: 469

Answers (3)

P.b
P.b

Reputation: 11483

Whole different approach, but how about:

=LET(f,ISNUMBER(C:C),
     c,FILTER(C:C,f),
     d,FILTER(D:D,f),
     s,SEQUENCE(COUNTA(d)),
MMULT(
      (d=TRANSPOSE(d))*(s>=TRANSPOSE(s)),
      c))

It first creates an array of TRUE's and FALSE's on column C:C if it contains a number. This is used to filter the values to be used from column C and D. A sequence of the count of filtered values in column C is used to simulate it's row number in that filtered range. Now MMULT checks row by row how many values of column D equal it's current value where the sequence number for that "row" is smaller or equal than the current.

enter image description here

Upvotes: 0

dohanin
dohanin

Reputation: 11

After rebooting and restarting excel, actually I could not reproduce the error. It works fine with the Lambda function now.

Upvotes: 1

Max R
Max R

Reputation: 828

I think you are going to hate me when you see this…

Put this in E2:

=SUMIF( B$2:B2, B2, C$2:C2 )

Then copy it down. Mind the dollar signs, they are important. You could place this in a named Lambda but the character count reduction is probably immaterial.

Upvotes: 1

Related Questions