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

    dipankar_de 

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

     

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

        dipankar_de 

        So you still have something to look forward to!  I couldn't wait, so put my semi-annual update aside and licenced a personal version (insider beta) in order to get to grips with the tumultuous changes.

        If you are prepared to accept relative referencing and copy-fill-down then the formula

        = SUM(
      XLOOKUP(@distinct,letter,number,,, 1)
    : XLOOKUP(@distinct,letter,number,,,-1)
  )

        will do the same job (green header).

    • dipankar_de's avatar
      dipankar_de
      Copper Contributor
      Jun 02, 2022
      Hello Peter, Many thanks for helping me out with this - exactly the solution I was looking for. I replaced the HSTACK with CHOOSE({1,2}, distinct, total) and it worked the way I wanted. However, I now need to implement this on the actual data I have and check the turnaround time. I will keep you posted if I encounter any issue. Thanks again for your help
      • dipankar_de's avatar
        dipankar_de
        Copper Contributor
        Jun 02, 2022
        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

Resources