Sort Option For Filter Function?

[shellp]

Hello

I am using Excel 365 and summarizing from a data table. Below gives me the data that I want but a) is it possible to programmatically format columns D and F with 1 decimal point and a % sign; and b) how can I programmatically sort column 6 (F) in descending order? Thanks for any and all assistance.

=LET(CC,(UNIQUE(FILTER(C_MRP,(C_Serv=J1)*(C_Date>=H1)*(C_Date<=H2)*(C_Denom>0)))),
CHOOSE({1,2,3,4,5,6,7},CC,COUNTIFS(C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0",C_Status,"=Y"),
COUNTIFS(C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0",C_Status,"=D"),
ROUND(COUNTIFS(C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0",C_Status,"=Y")*100/SUMIFS(C_Denom,C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0"),1),
COUNTIFS(C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0",C_Status,"=N"),
ROUND(COUNTIFS(C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0",C_Status,"=N")*100/SUMIFS(C_Denom,C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0"),1),
SUMIFS(C_Denom,C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0")))

Results (minus the sort):

 

[StephenCrump]

In simple terms: =LET(x,SomeArrayFormula,SORT(x,6,-1))

Or in your case, more like: =LET(CC,YourFormula,x,YourBigFormulaUsingCC,SORT(x,6,-1))

But your formula looks unnecessarily complicated. You're filtering C_MRP on four criteria, but then building those same criteria into several COUNTIFS and SUMIFS?

...is it possible to programmatically format columns D and F with 1 decimal point and a % sign

I'm not clear what you mean by programmatically? Any reason you can't simply manually format the cells?

You can use the TEXT function in a formula to display a number in a particular format. This can be handy when you want to incorporate a number into text, e.g. the first example below.
But I wouldn't use this to "format" numbers, because then they are no longer numbers, e.g 2nd example below.

	A	B
1	19 Dec 2023	Today is Tuesday
2	0.0143	1.4%
Sheet1

Cell Formulas
Range	Formula
A1	A1	=TODAY()
B1	B1	="Today is "&TEXT(A1,"dddd")
B2	B2	=TEXT(A2,"0.0%")

 

[shellp]

Hello,

Thank you so much for your reply. I am just learning about this function so I am not understanding what you mean by it being unnecessarily complicated....how can I get MRP but then the calculated columns as well? Thanks.

Shelley

 

[Fluff]

Looks like you can slim down the formula like

=LET(CC,(UNIQUE(FILTER(C_MRP,(C_Serv=J1)*(C_Date>=H1)*(C_Date<=H2)*(C_Denom>0)))),
a,COUNTIFS(C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0",C_Status,"=Y"),
b,SUMIFS(C_Denom,C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0"),
c,COUNTIFS(C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0",C_Status,"=N"),
SORT(CHOOSE({1,2,3,4,5,6,7},CC,a,COUNTIFS(C_Serv,J1,C_MRP,CC,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0",C_Status,"=D"),ROUND(a*100/b,1),c,ROUND(c*100/b,1),b),6,-1))

 

[StephenCrump]

... I am not understanding what you mean by it being unnecessarily complicated....

I should have said unnecessarily long, which was my quick impression based on the same filters being applied several times.

A shorter version (and probably able to be further shortened) that should work for you?

=LET(CC,FILTER(CHOOSE({1,2},C_MRP,C_Status),(C_Serv=J1)*(C_Date>=H1)*(C_Date<=H2)*(C_Denom>0)),nm,TAKE(CC,,1),status,TAKE(CC,,-1),u,UNIQUE(nm),Y,BYROW(u,LAMBDA(r,SUM((status="Y")*(nm=r)))),N,BYROW(u,LAMBDA(r,SUM((status="N")*(nm=r)))),D,BYROW(u,LAMBDA(r,SUM((status="D")*(nm=r)))),T,Y+N+D,SORT(CHOOSE({1,2,3,4,5,6,7},u,Y,D,Y/T,N,N/T,T),6,-1))

 

[StephenCrump]

Or another way without using FILTER:

=LET(c,COUNTIFS(C_MRP,C_MRP,C_Status,{"Y","D","N"},C_Serv,J1,C_Date,">="&H1,C_Date,"<="&H2,C_Denom,">0"),x,UNIQUE(HSTACK(C_MRP,c,BYROW(c,LAMBDA(r,SUM(r))))),t,TAKE(x,,-1), SORT(HSTACK(TAKE(x,,3),INDEX(x,,2)/t,INDEX(x,,4),INDEX(x,,4)/t,t),6,-1))