0

I want a cumulative formula that will return the running total for each value in column B.

For example, in the rows in column A:

  1. =SUM(B$1:B1)
  2. =SUM(B$1:B2)
  3. =SUM(B$1:B3)
  4. =SUM(B$1:B4)
  5. etc.

I would like to get get this as an array formula so that I don’t have to drag the formula every time I add more data.

I haven't been able to solve this myself.

example

1 Answer 1

1

SCAN Function

Scans an array and produces intermediate values by application of a LAMBDA function to each value.

TOCOL was used to remove blank rows from column B.

=SCAN(0, TOCOL(B2:B,1), LAMBDA(a,v, a+v)) 

MAP w/ SEQUENCE

  1. MAP a SEQUENCE of numbers matching the number of populated rows in your column, one by one, into a LAMBDA function that stores the current row number in x
  2. The LAMBDA's formula takes x and creates another SEQUENCE of numbers up to and including x that CHOOSEROWS uses as the row numbers from column B to return.
  3. The CHOOSEROWS function is wrapped in SUM which returns the total up to that row x, then the process repeats for the next x until they are all done.
=MAP(SEQUENCE(COUNTA(B1:B)), LAMBDA(x, 
   SUM(CHOOSEROWS(B1:B, SEQUENCE(x))))) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.