 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 exist on a sheet, it's calculated in a LET expression and in reality can involve more columns and rows

Question: is there a way to i.e. SUM that array row by row and get the below Expected arr (not on a sheet)? Thanks
(I posted the question yesterday at a time where the site had issues. Can't find that post today...)

21 Replies
best response confirmed by L z. (Super Contributor)
Solution

# Re: Excel 365 SUM dynamic array at row level

That's a classic MMULT() solution.

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

# Re: Excel 365 SUM dynamic array at row level

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?

# Re: Excel 365 SUM dynamic array at row level

If for the range in grid

``=SUBTOTAL({5,4},OFFSET(INDEX(A2:C4,1,1),SEQUENCE(ROWS(A2:C4),,0),,,COLUMNS(A2:C4)))``

# Re: Excel 365 SUM dynamic array at row level

Nice one Sergei. Thank you too. I'll keep this thread (oups, Discussion) open for a few days in case someone would have a bright idea to make it work off the grid
Thanks again

# Re: Excel 365 SUM dynamic array at row level

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.

# Re: Excel 365 SUM dynamic array at row level

Sum which @Detlef Lewin suggested shall work out of grid, like

``=MMULT(array,SEQUENCE(COLUMNS(array),1,1,0))``

Min and Max based on OFFSET definitely won't work, afraid we need lambdas here. That's interesting task, will try to play with it.

# Re: Excel 365 SUM dynamic array at row level

Perhaps for an array of max values:

``````=LET(
i,SEQUENCE(ROWS(array)),
m,COLUMNS(array),
arrayRank,i+PERCENTRANK.EXC(array,array,15),
rowRank,SMALL(arrayRank,i*m),
rowTot,MMULT(array*(arrayRank=rowRank),1^i),
rowFreq,MMULT(N(arrayRank=rowRank),1^i),
rowTot/rowFreq
)``````

Change i*m to (i-1)*m+1 for array of min values

In the given example:

array ={1,2,3;10,20,30;100,200,300}
arrayRank ={1.1,1.2,1.3;2.4,2.5,2.6;3.7,3.8,3.9}
rowRank ={1.3;2.6;3.9}
rowTot ={3;30;300}

The result is divided by the number of occurences of the max value if there is more than one.

# Re: Excel 365 SUM dynamic array at row level

Not sure that always works correctly

``````=LET(
maxN,  MAX(array)+SIGN(MIN(array))*MIN(array),
m,     COLUMNS(array),
r,     ROWS(array),
i,     SEQUENCE(r),
arrayM, i+array/maxN,
sumR, MMULT(array,SEQUENCE(m,1,1,0)),
largeR, LARGE(arrayM,(SEQUENCE(r,,r,-1)-1)*m+1) - i,
smallR, SMALL(arrayM,(i-1)*m+1) - i,
CHOOSE({1,2,3}, sumR, smallR*maxN, largeR*maxN))``````

# Re: Excel 365 SUM dynamic array at row level

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. @L z. 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)``

# Re: Excel 365 SUM dynamic array at row level

That looks ok for 'normal' values, that is
- at least one positive value in grid (eg not all zero or all -1)
- values do not differ by too many orders of magnitude (eg array doesn't contain both 1E+20 and 1)

Another option that might avoid division by zero and floating point inaccuracies,

``````=LET(
i,SEQUENCE(ROWS(array)),
m,COLUMNS(array),
n,COUNT(array),
rank,MATCH(array,SMALL(array,SEQUENCE(n))),
rowRank,MOD(SMALL(n*i+rank-1,i*m),n)+1,
SMALL(array,rowRank))``````

# Re: Excel 365 SUM dynamic array at row level

Yes, you are right. First two (all zeroes or all same negative) could be solved if add any number to maxN, e.g. 1. But rounding and big numbers is the real issue.

Your solution is much more reliable and looks very nice, will play with it some later.

# Re: Excel 365 SUM dynamic array at row level

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

# Re: Excel 365 SUM dynamic array at row level

Tried to repeat that from scratch, manipulating with indexes is not my strong point. Thus nothing new is here

``````=LET(
n,     ROWS(array),
m,     COLUMNS(array),
nm,    n*m,
k,     SEQUENCE(n),
j,     SEQUENCE(nm),
sPos,  XMATCH(array,SMALL(array, j )),
lPos,  XMATCH(array,LARGE(array, j)),
sSec,  MOD(SMALL(nm*k+sPos-1, k*m),nm)+1,
lSec,  MOD(SMALL(nm*k+lPos-1, k*m),nm)+1,
sumR,  MMULT(array,SEQUENCE(m,1,1,0)),
CHOOSE({1,2,3},sumR, LARGE(array,lSec), SMALL(array,sSec)))``````

# Re: Excel 365 SUM dynamic array at row level

Indeed, that formula follows similar logic. I learnt from this exercise several alternatives to RANK(array,array,-1) exist which permit arrays in arguments including,

=MATCH(array,SMALL(array,SEQUENCE(COUNT(array))))

=PERCENTRANK.EXC(array,array,15)*COUNT(array,)

I like the use of dynamic criteria here, it might even open the door to database functions like

=DSUM(Ref,j,Crit)

for an array of column sums (where Ref and Crit are dynamic references and j={1,2,...,m})

# Re: Excel 365 SUM dynamic array at row level

couple more variations attached..

# Re: Excel 365 SUM dynamic array at row level

To all. I feel extremely embarrased that you spent time & efforts on the MIN/MAX as it was curiosity only on my side. And worst, I won't be able to mark more that 1 reply as best answer (expect if I missed something). In any case in big THANK YOU to all of you, I'll definitively learn from what you shared

(in a-z order) @lori_m
On a couple of occasions I got a #VALUE! error with your version involving PERCENTRANK.EXC (might be me...). The one in the workbook you shared seems OK after a few checks. Like your DSUM, DMIN & DMAX approach

@Peter Bartholomew
Thanks for sharing your workbook. Like the approach and will learn from it

@Sergei Baklan
Re. helper in grid is very often real helper, don't know why people try to avoid it and overcomplicate formulas
In case the msg was for me I'm not at all against helper columns/rows or hidden helper sheets, I use them quite often
Your last formula with XMATCH & MOD seems OK as well after a few checks

You guys are amazing
THANKS MUCH AGAIN

# Re: Excel 365 SUM dynamic array at row level

@L z. , thanks. Nope, my message was not for you, sorry if I articulate it wrongly. That was just an observation in general.

# Re: Excel 365 SUM dynamic array at row level

No worries at all and even if it was for me I would have taken the point (positively)
Nice day...

# Re: Excel 365 SUM dynamic array at row level

Since you now have Lambda functions, the solution simplifies.  First, assuming the dummy array has been given a name

``````= BYROW(Dummy_arr, Sumλ)

where

Sumλ = LAMBDA(x,SUM(x))``````

or including the array definition within a LET

``````= LET(
dummyArr, {1;10;100}*SEQUENCE(1,3),
BYROW(dummyArr, Sumλ)
)``````

I have attached a file, but it contains little by way of additional information.