0

I am trying to use the SortBy function to return the top 3 results from a table that I have. If I put the function somewhere else in the sheet, I can just reference the first three cells, and that works fine. But I would like to do this with just one function.

I have created a lambda with the Filter function to help, but if I get multiple items with the same sum, it will give me more than three results and cause a spill. Is there another way I can approach this to give me the top 3 know that I might not see some ties if they are there.

    =LAMBDA(rDesc,rSum,top,default,
LET(
    sDesc,SORTBY(UNIQUE(rDesc),SUMIFS(rSum,rDesc,UNIQUE(rDesc)),-1),
    sSum,SORT(SUMIFS(rSum,rDesc,UNIQUE(rDesc)),,-1),
    range,AppenColRange(sDesc,sSum,"-"),
    rowindex,SEQUENCE(top)
    result,FILTER(range,sSum>=LARGE(sSum,top),"-"),
    topResults,IF(
        rowindex<=
    )

    IFERROR(result,default)))

The data would look something like this.

Food      Sold
Apple     20
Banana    10
Apple     30
Apple     3
Carrot    5
Milk      10
Peas      10
Kale      1

Then I'm looking for a result like this

Apple     53
Banana    10
Milk      10

Peas also had ten, but got cut because I only want 3 items returned.

ZygD
  • 22,092
  • 39
  • 79
  • 102
DChi Shaggy
  • 22
  • 2
  • 5
  • 1
    Ok So what Scott Sir, has shown is robust, compact better and less verbose, I tried the way you were trying to achieve, and it can be worked out in this way, `=LAMBDA(rDesc,rSUM,Top,Default, LET(sDesc,SORTBY(UNIQUE(rDesc),SUMIFS(rSUM,rDesc,UNIQUE(rDesc)),-1), sSUM,SORT(SUMIFS(rSUM,rDesc,UNIQUE(rDesc)),,-1), range,CHOOSE({1,2},sDesc,sSUM), FILTER(range,NOT(ISNA(INDEX(range,,2)=LARGE(INDEX(range,,2),SEQUENCE(Top)))),Default)))(A2:A9,B2:B9,3,"")` – Mayukh Bhattacharya Aug 29 '22 at 17:36
  • 1
    I appreciate you correcting what I had. – DChi Shaggy Aug 29 '22 at 19:19

1 Answers1

4

We can use TAKE and HSTACK:

=LAMBDA(rdesc,rsum,top,default,
    LET(unq,UNIQUE(rdesc),
    IFERROR(TAKE(SORT(HSTACK(unq,SUMIFS(rsum,rdesc,unq)),2,-1),top),default)))

enter image description here

And since at the time of writing this HSTACK and TAKE are not available to all Office 365 users, we can use CHOOSE and INDEX respectively:

=LAMBDA(rdesc,rsum,top,default,
    LET(unq,UNIQUE(rdesc),
    IFERROR(INDEX(SORT(CHOOSE({1,2},unq,SUMIFS(rsum,rdesc,unq)),2,-1),SEQUENCE(top),{1,2}),default)))

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Wow, that greatly simplifies what I was trying to do. It looks like I have the TAKE and HSTACK functions, but I'm going to use your second solution for now, just in case others in my facility don't have those functions. Looking at Microsoft's examples for Choose, I'm not sure where the {1,2} comes in to play. – DChi Shaggy Aug 29 '22 at 17:30