Excel running total / cumulative sum for each row of a dynamic array

Question

i'm building an excel model using dynamic arrays which may expand horizontally or vertically depending on the inputs

Example of the array:

1	2	3	4
5	6	7	8

I'm trying to calculate the horizontal cumulative sum / running total for each row of an array. I've tried the Scan/Lamda combination explained here: Cumulative Sum Formula using new Excel Dynamic Array Formulas

This leads to a cumulative sum over the complete array, not for each individual row:

1	3	6	10
15	21	28	36

Is it possible to achieve a cumulative sum for each row? The solution should look like this:

1	3	6	10
5	11	18	26

Thanks

Microscopy

Answer 1

Why not just employ some simple matrix multiplication?

=LET(ζ,A1:D2,ξ,COLUMNS(ζ),MMULT(ζ,N(SEQUENCE(ξ)<=SEQUENCE(,ξ))))

Sommer

Thanks! this seems to work. Didnt think of MMULT(). – Microscopy

Answer 2

I initially thought that you could do it by using Scan nested within Byrow, but this results in a 'does not support nested arrays' error.

You can do it fairly easily using a combination of Makearray and Index:

=MAKEARRAY(
    2,
    4,
    LAMBDA(r, c,
        SUM(
            INDEX(sheet1!A1:D2, r, 1) : INDEX(sheet1!A1:D2, r, c)
        )
    )
).

It appears that you cannot define the range in a Let statement and pass it through to the lambda like this:

=LET(
    range, Sheet1!A1:D2,
    MAKEARRAY(
        2,
        4,
        LAMBDA(r, c,
            SUM(INDEX(range, r, 1) : INDEX(range, r, c))
        )
    )
)

results in

But you can write the first formula more generally by defining A1:D2 as a named range e.g. range1

=MAKEARRAY(
    ROWS(range1),
    COLUMNS(range1),
    LAMBDA(r, c, SUM(INDEX(range1, r, 1) : INDEX(range1, r, c)))
)

Inadequate