Limit the number of results when using Excel array functions

Question

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.

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
– Mayukh Bhattacharya, Commented Aug 29, 2022 at 17:36

BigBen · Accepted Answer · 2022-08-29 16:21:10Z

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

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

edited Aug 29, 2022 at 16:21

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

answered Aug 29, 2022 at 16:12

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DChi Shaggy Over a year ago

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.

Collectives™ on Stack Overflow

Limit the number of results when using Excel array functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related