Cumulative Sum Formula using new Excel Dynamic Array Formulas

Cumulative Sum Formula: Using LAMBDA() helper functions SCAN() or MAP() or BYROW() --> Dynamic Spill Array Formulas, exclusively works with ^{MS365 Current Channel Versions}

• Method 1: Formula used in cell B1 --> Using SCAN()

=SCAN(0,A1:A8,LAMBDA(x,y,x+y))

• Method 2: Formula ^{can be} used in cell B1 --> Using VSTACK() & SCAN()

=VSTACK(A1,SCAN(A1,A2:A8,LAMBDA(x,y,x+y)))

• Method 3: Formula ^{can be} used in cell B1 --> Using LET() with SCAN()

=LET(x,A1:A8, sum,LAMBDA(z,y,z+y), SCAN(0,x,sum))

• Method 4: Formula ^{can be} used in cell B1 --> Using MAP()

=MAP(A1:A8,LAMBDA(x,SUM(A1:x)))

• Method 5: Formula ^{can be} used in cell B1 --> Using BYROW

=BYROW(A1:A8,LAMBDA(α,SUM(A1:α)))

Update: 11/15/2023

SCAN() function doesn't needs anymore LAMBDA() construct, only needs to reference single particular Excel Function directly. Here is an example below. Courtesy : Thanks To JvdV Sir.

• Formula used in cell B1

=SCAN(0,A1:A8,SUM)

_{NOTES: Formulas are updated in the post on 4/2/2024 and works with recent/current versions of MS365}

Here's another approach using Matrix Multiplication

=MMULT(TRANSPOSE((ROW(I3#) <= TRANSPOSE(ROW(I3#)))*I3#),SIGN(I3#))

New Answer 2022-06-20

For a dynamic array in A1 use:

=MMULT(N(ROW(A1#)>=TRANSPOSE(ROW(A1#))),A1#)

If the dynamic array in A1 has multiple columns, this returns the cumulative sum of each column, unlike my more complicated Original Answer.

Original Answer 2022-01-13

By using SIGN(I3#), chris neilsen's solution accumulates absolute values. To accommodate negative numbers, replace SIGN(I3#) with 1*(I3#=I3#) in your cumulative sum:

=MMULT(TRANSPOSE((ROW(I3#) <= TRANSPOSE(ROW(I3#)))*I3#),1*(I3#=I3#))

Alternatively, generate a dynamic array of ones with

SEQUENCE(ROWS(I3#),,,0) instead of 1*(I3#=I3#).

(I lack the reputation to comment.)

Here's one way I've done it, but wondering if there's an easier/more efficient way to do it...

=SUBTOTAL(9,OFFSET(A1#,0,0,SEQUENCE(COUNT(A1#))))

This one solves a rolling twelve sum

=SUBTOTAL(9,OFFSET(A1#,SEQUENCE(COUNTA(A1#),1,0),0,12))

You can also use the following:

=SUBTOTAL(9,INDIRECT(CELL("Address",A1)&":"&CELL("address",OFFSET(A1,SEQUENCE(NumberOfRows,0,0,1),0))))

Assume in the above that cell A1 contains the spill formula for the values you want to sum. You can also use a helper column for SEQUENCE(), and if you did that in column B you would use:

=SUBTOTAL(9,INDIRECT(CELL("Address",A1)&":"&CELL("address",OFFSET(A1,B1#,0))))

THIS ALSO WORKS FOR A CUMULATIVE PRODUCT, IF YOU USE =SUBTOTAL(6,...)

I found that the answers given above didn't work for me.

• For the matrix multiplication, it works for cumulative sums but it doesn't work for cumulative products. So my answer is a bit more generalised.
• For the earlier answers using SUBTOTAL(), for some reason I had to add a number to the values generated by SEQUENCE(). In my case, I had to add 3 which just seemed random. I could find no reason for having to add it, but without it the cumulative sum was out by 3 rows.

My answer doesn't seem to work for the PRODUCT() and SUM() formulae, only for SUBTOTAL(). I'd guess that PRODUCT() and SUM() haven't yet been changed to spill/array type formulae, but SUBTOTAL has.