Getting Top x For Spill Formula?

[shellp]

Hello,

Below is the code that works(and a resulting picture) but it shows all that match the criteria. How can I edit the code so that it only displays the top 5 groups by volumes of cases?

=LET(CC,(UNIQUE(FILTER(raw[HIG_Short_Desc],(raw[MRP]=Report_Doc)*(raw[Fyear]=Sel_Year)))),
a,COUNTIFS(raw[MRP],Report_Doc,raw[Fyear],"="&Sel_Year,raw[HIG_Short_Desc],CC),
b,SUMIFS(raw[totaldays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year),
c,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year),
d,COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
e,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
f,SUMIFS(raw[HIG_ELOS_R],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
g,COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
h,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
i,SUMIFS(raw[HIG_ELOS_R],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
SORT(CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12,13,14},CC,a,ROUND(b/a,1),ROUND(c/a,1),COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),ROUND(d*100/a,1),IFERROR(ROUND(e/d,1),0),IFERROR(ROUND(f/d,1),0),IFERROR((ROUND(e/d,1))/(ROUND(f/d,1)),1),g,ROUND(g*100/a,1),IFERROR(ROUND(h/g,1),0),IFERROR(ROUND(i/g,1),0),IFERROR((ROUND(h/g,1))/(ROUND(i/g,1)),0),1),2,-1))

 

[Fluff]

What version of Excel are you using, as neither 2010 or 2007 have some of those functions?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[shellp]

Sorry about that...I edited my account. The code in my post is is Excel 365.

 

[Fluff]

Ok, how about

=LET(CC,(UNIQUE(FILTER(raw[HIG_Short_Desc],(raw[MRP]=Report_Doc)*(raw[Fyear]=Sel_Year)))),
a,COUNTIFS(raw[MRP],Report_Doc,raw[Fyear],"="&Sel_Year,raw[HIG_Short_Desc],CC),
b,SUMIFS(raw[totaldays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year),
c,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year),
d,COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
e,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
f,SUMIFS(raw[HIG_ELOS_R],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),
g,COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
h,SUMIFS(raw[acutedays],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
i,SUMIFS(raw[HIG_ELOS_R],raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Long Stay"),
TAKE(SORT(HSTACK(CC,a,ROUND(b/a,1),ROUND(c/a,1),COUNTIFS(raw[MRP],Report_Doc,raw[HIG_Short_Desc],CC,raw[Fyear],"="&Sel_Year,raw[Grp2],"=Typical"),ROUND(d*100/a,1),IFERROR(ROUND(e/d,1),0),IFERROR(ROUND(f/d,1),0),IFERROR((ROUND(e/d,1))/(ROUND(f/d,1)),1),g,ROUND(g*100/a,1),IFERROR(ROUND(h/g,1),0),IFERROR(ROUND(i/g,1),0),IFERROR((ROUND(h/g,1))/(ROUND(i/g,1)),0),1),2,-1),5))

 

[shellp]

You are brilliant! Thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback.