Forum Discussion

dipankar_de's avatar
dipankar_de
Copper Contributor
Jun 02, 2022

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 ...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 02, 2022

dipankar_de 

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.

 

dipankar_de's avatar
dipankar_de
Copper Contributor
Jun 03, 2022
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"?

Resources