Forum Discussion
Help with MMULT for a range containing 100,000+ values
I have been trying to solve a problem for some time now but unable to find an acceptable solution and hence requesting help from you. The attached has a snapshot of what I am trying to achieve. The ...
SUMIFS is very fast in execution and tends to beat array methods. However, provided your data is strictly sorted, it is possible to search for the first and last entries of each contiguous range and sum that.
= LET(
distinct, SORT(UNIQUE(letter)),
total, MAP(distinct, LAMBDA(chr,
LET(
firstCell, XLOOKUP(chr,letter,number,,,1),
finalCell, XLOOKUP(chr,letter,number,,,-1),
SUM(firstCell:finalCell)
)
)
),
HSTACK(distinct, total)
)I have beaten SUMIFS with formulas of this kind in the past in situations in which each summation is only working off a small proportion of the values.
- Hello Peter, if, instead of one "number" column, we have, say 5 "number" columns, how would the formula change to accommodate summing up the 5 column values independently for each unique "letter"?
- Hello Peter, Many thanks for the help you extended. Unfortunately, I don't have the version that supports LAMBDA, HSTACK and others that are still in Beta. Hence, am limited to functions that are available in the Released O365.
Sorry, the HSTACK was little more than cosmetic tidying up. This is the same function stripped right back to a relative reference.
= SUM(XLOOKUP(@distinct,letter,number,,, 1):XLOOKUP(@distinct,letter,number,,,-1))- Hello Peter, The version of excel that I have for implementing the actual solution is Version 2202 (Build 14931.20132) Semi -Annual Enterprise Channel (Preview) and it appears that the LAMBDA functions are not yet there in the said version.
Is there a way to implement the solution without MAP / LAMBDA.
