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

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}

---