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

7 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    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))
    )
)
    • ds100's avatar
      ds100
      Copper Contributor
      Feb 25, 2022
      Brilliant. I'm going to get a lot of use out of that. Thank you
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Feb 25, 2022
        Glad I could help & you have LAMBDA & Co. + Thanks for providing feedback

Resources