excelexcel-formulaoffice365excel-lambda

Limit the number of results when using Excel array functions

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.

Solution

  • 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