SOLVED

Excel 365 SUM dynamic array at row level

%3CLINGO-SUB%20id%3D%22lingo-sub-2456557%22%20slang%3D%22en-US%22%3EExcel%20365%20SUM%20dynamic%20array%20at%20row%20level%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2456557%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E(no%20LAMDBA%20here).%20I've%20been%20struggling%20with%20this%20for%20a%20moment%2C%20searched%20here%20%26amp%3B%20there%20for%20something%20similar%20to%20no%20avail.%3CBR%20%2F%3EIn%20the%20below%20pic.%20%3CEM%3E%3CSTRONG%3EDummy%26nbsp%3B%3C%2FSTRONG%3E%3CSTRONG%3Earr%3C%2FSTRONG%3E%3C%2FEM%3E%20is%20the%20representation%20of%20an%20array%20that%20doesn't%20exist%20on%20a%20sheet%2C%20it's%20calculated%20in%20a%20LET%20expression%20and%20in%20reality%20can%20involve%20more%20columns%20and%20rows%3C%2FP%3E%3CP%3EQuestion%3A%20is%20there%20a%20way%20to%20i.e.%20SUM%20that%20array%20row%20by%20row%20and%20get%20the%20below%20%3CFONT%20color%3D%22%23339966%22%3E%3CEM%3E%3CSTRONG%3EExpected%20arr%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FFONT%3E%20(not%20on%20a%20sheet)%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Demo.png%22%20style%3D%22width%3A%20289px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F289444iA087A886418833C0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Demo.png%22%20alt%3D%22Demo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3CBR%20%2F%3E(I%20posted%20the%20question%20yesterday%20at%20a%20time%20where%20the%20site%20had%20issues.%20Can't%20find%20that%20post%20today...)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2456557%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2456603%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20SUM%20dynamic%20array%20at%20row%20level%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2456603%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20a%20classic%20MMULT()%20solution.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMMULT(A2%3AC4%3BSEQUENCE(COLUMNS(A2%3AC4))%5E0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2456823%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20SUM%20dynamic%20array%20at%20row%20level%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2456823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EThanks%20much%3C%2FU%3E.%20Classic%2C%20classic%2C%20for%20you%20%3A))%3C%2FP%3E%3CP%3EI'm%20more%20used%20to%20do%20a%20SIGN(SEQUENCE(COLUMNS(A2%3AC4)))%20but%20got%20it%20now%20and%20did%20what%20I%20actually%20needed%20to%20do%20as%20well%20(Average)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20don't%20see%20how%20a%20MIN%20or%20MAX%20would%20be%20doable.%20Surely%20you%20have%20another%20classic%20%3A))%20trick%20for%20this%2C%20don't%20you%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2459671%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20SUM%20dynamic%20array%20at%20row%20level%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2459671%22%20slang%3D%22en-US%22%3ENice%20one%20Sergei.%20Thank%20you%20too.%20I'll%20keep%20this%20thread%20(oups%2C%20Discussion)%20open%20for%20a%20few%20days%20in%20case%20someone%20would%20have%20a%20bright%20idea%20to%20make%20it%20work%20off%20the%20grid%3CBR%20%2F%3EThanks%20again%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2459777%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20SUM%20dynamic%20array%20at%20row%20level%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2459777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20classic.%20SUBTOTAL(OFFSET()).%3C%2FP%3E%3CP%3EI%20always%20struggle%20with%20this%20one%20because%20formula%20evaluation%20returns%20%23VALUE!%20at%20a%20certain%20point.%20Nevertheless%20the%20final%20result%20is%20correct.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464822%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20SUM%20dynamic%20array%20at%20row%20level%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20for%20an%20array%20of%20max%20values%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0Ai%2CSEQUENCE(ROWS(array))%2C%0Am%2CCOLUMNS(array)%2C%0AarrayRank%2Ci%2BPERCENTRANK.EXC(array%2Carray%2C15)%2C%0ArowRank%2CSMALL(arrayRank%2Ci*m)%2C%0ArowTot%2CMMULT(array*(arrayRank%3DrowRank)%2C1%5Ei)%2C%0ArowFreq%2CMMULT(N(arrayRank%3DrowRank)%2C1%5Ei)%2C%0ArowTot%2FrowFreq%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20i*m%20to%20(i-1)*m%2B1%20for%20array%20of%20min%20values%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20given%20example%3A%3C%2FP%3E%3CP%3Earray%20%3D%7B1%2C2%2C3%3B10%2C20%2C30%3B100%2C200%2C300%7D%3CBR%20%2F%3EarrayRank%20%3D%7B1.1%2C1.2%2C1.3%3B2.4%2C2.5%2C2.6%3B3.7%2C3.8%2C3.9%7D%3CBR%20%2F%3ErowRank%20%3D%7B1.3%3B2.6%3B3.9%7D%3CBR%20%2F%3ErowTot%20%3D%7B3%3B30%3B300%7D%3C%2FP%3E%3CP%3EThe%20result%20is%20divided%20by%20the%20number%20of%20occurences%20of%20the%20max%20value%20if%20there%20is%20more%20than%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2464853%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20SUM%20dynamic%20array%20at%20row%20level%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2464853%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20that%20always%20works%20correctly%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0AmaxN%2C%20%20MAX(array)%2BSIGN(MIN(array))*MIN(array)%2C%0Am%2C%20%20%20%20%20COLUMNS(array)%2C%0Ar%2C%20%20%20%20%20ROWS(array)%2C%0Ai%2C%20%20%20%20%20SEQUENCE(r)%2C%0AarrayM%2C%20i%2Barray%2FmaxN%2C%0AsumR%2C%20MMULT(array%2CSEQUENCE(m%2C1%2C1%2C0))%2C%0AlargeR%2C%20LARGE(arrayM%2C(SEQUENCE(r%2C%2Cr%2C-1)-1)*m%2B1)%20-%20i%2C%0AsmallR%2C%20SMALL(arrayM%2C(i-1)*m%2B1)%20-%20i%2C%0ACHOOSE(%7B1%2C2%2C3%7D%2C%20sumR%2C%20smallR*maxN%2C%20largeR*maxN))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Regular Contributor

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


Demo.png

 

Thanks
(I posted the question yesterday at a time where the site had issues. Can't find that post today...)

18 Replies
best response confirmed by L z. (Regular Contributor)
Solution

@L z. 

That's a classic MMULT() solution.

 

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

 

@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?

@L z. 

If for the range in grid

=SUBTOTAL({5,4},OFFSET(INDEX(A2:C4,1,1),SEQUENCE(ROWS(A2:C4),,0),,,COLUMNS(A2:C4)))
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

@Sergei Baklan 

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.

 

@L z. 

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.

@L z. 

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.

@L z. 

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

@L z. 

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!

 

image.png

where 'mask' is defined to by

= IF(dummyArr#,k)

@Sergei Baklan 

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

 

@lori_m 

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.

@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.

@lori_m 

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

@Sergei Baklan 

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

 

@Peter Bartholomew 

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

couple more variations attached..

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 

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

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