Cumulative Sum Formula using new Excel Dynamic Array Formulas

Question

I'm using the new Dynamic (ie Spill) formulas in Excel. I want a dynamic array that is the accumulation or running total of another dynamic array.

Let's assume I have the following dynamic data in A1:A8:

12
20
14
13
12
13
26
11

To find the differences in this array is trivial:

=OFFSET(A1#,1,0)-A1#

8
-6
-1
-1
1
13
-15
-11

But how do I get the running total using the new dynamic formulas?

12
32
46
59
71
84
110
121

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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}

---

Answer 5

This one solves a rolling twelve sum

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

Answer 6

Here's another approach using Matrix Multiplication

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