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.
Thank you in advance for any help you can provide me.
CodePudding user response:
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)))