Forum Discussion

ds100's avatar
ds100
Copper Contributor
Feb 24, 2022
Solved

Cumulative Sum by column for two dimensional dynamic arrays?

Referencing a two dimensional dynamic array, how would I output a running total two dimensional array which calculates cumulative sums by column? Thanks in advance.  Daniel
excel
  • Lorenzo's avatar
    Lorenzo
    Feb 25, 2022

    ds100 

     

    in E3

    =MAKEARRAY(
    ROWS(TodayArray), COLUMNS(TodayArray),
    LAMBDA(r,c,
        SUM( INDEX(TodayArray,SEQUENCE(r),c) )
    )
)
Daniel2010's avatar
Daniel2010
Copper Contributor
Oct 06, 2022

Lorenzo 

Can you do it the other way around?

TodayArrayRunningTotals
1213
2426
3639
48412
510515

The solution is awesome

Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 07, 2022

Alternative for Excel 2021 where MAKEARRAY isn't available

 

 

in F2:

=SUBTOTAL(9,
    OFFSET(B2,,
        SEQUENCE(,COLUMNS(B2:D6),0),
        SEQUENCE(ROWS(B2:D6))
    )
)

in J2:

=SUBTOTAL(9,
    OFFSET(B2,
        SEQUENCE(ROWS(B2:D6),,0),
        ,,
        SEQUENCE(,COLUMNS(B2:D6))
    )
)

Resources