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)

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 19, 2021

Lorenzo 

Some neat tricks, which address one of the weaknesses of dynamic arrays which still cause developers to revert to the flawed concepts of copying formulas with relative references!

 

In normal use, array formulas largely replace the need for helper ranges, so reducing the risk of error.  In the attached, I have taken a different strategy and allowed the array formulas to write to dynamic ranges to a hidden helper sheet so that the xxxIFS family of functions may be used.

 

p.s. Lorenzo Matrix equations are not that scary!  Because of the nature of my former employment, I have probably written more matrix equations than I have English language paragraphs!

 

where 'mask' is defined to by

= IF(dummyArr#,k)
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 19, 2021

PeterBartholomew1 , helper in grid is very often real helper, don't know why people try to avoid it and overcomplicate formulas. I like your solution.

Resources