Forum Discussion

Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 17, 2021
Solved

Excel 365 SUM dynamic array at row level

Hi (no LAMDBA here). I've been struggling with this for a moment, searched here & there for something similar to no avail. In the below pic. Dummy arr is the representation of an array that doesn't...
excel
Formulas and Functions
office 365
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jun 17, 2021

    Lorenzo 

    That's a classic MMULT() solution.

     

    =MMULT(A2:C4,SEQUENCE(COLUMNS(A2:C4))^0)

     

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Jun 17, 2021

Lorenzo 

That's a classic MMULT() solution.

 

=MMULT(A2:C4,SEQUENCE(COLUMNS(A2:C4))^0)

 

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 17, 2021

    Detlef_Lewin 

     

    Thanks much. Classic, classic, for you :))

    I'm more used to do a SIGN(SEQUENCE(COLUMNS(A2:C4))) but got it now and did what I actually needed to do as well (Average)

     

    However, I don't see how a MIN or MAX would be doable. Surely you have another classic :)) trick for this, don't you?

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 17, 2021

      Lorenzo 

      If for the range in grid

      =SUBTOTAL({5,4},OFFSET(INDEX(A2:C4,1,1),SEQUENCE(ROWS(A2:C4),,0),,,COLUMNS(A2:C4)))
      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Jun 17, 2021

        SergeiBaklan 

        Another classic. SUBTOTAL(OFFSET()).

        I always struggle with this one because formula evaluation returns #VALUE! at a certain point. Nevertheless the final result is correct.

         

Resources