Dynamic Arrays - Top 5 values

[Leticia]

Hi! I am having some difficulties with my dynamic arrays

I need to create two different dynamic arrays, in which I extract the Top 5 values aggregated by "Case".

In the first scenario, I would need to obtain the Top 5 values when SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4)+SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4)
In the second scenario, I would need to obtain the Top 5 values when SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSSING";TEST!$A:$A;"<="&$F$4)+SUMIFS(TEST!$F:$F;TEST!$E:$E;"<>R.";TEST!$E:$E;"<>7250";TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$D:$D;"<"&EDATE($F$4;1))

However, when creating my dynamic arrays, I am not capable of creating one for each scenario. Instead I created 3 different dynamic arrays which are not useful as omit part of the equation.

=IFERROR(INDEX(SORTBY(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!C:C="MISSING")*(TEST!$A:$A<=$F$4))))));SUMIFS(TEST!$F:$F;TEST!$C:$C;"MISSING";TEST!$A:$A;"<="&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$C:$C="MISSING")*(TEST!$A:$A<=$F$4)))));-1);SEQUENCE(5;1;1);{1\2});"-")

=IFERROR(INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4))))));2;-1);SEQUENCE(5;1;1);{1\2});"-")

=IFERROR(INDEX(SORT(CHOOSE({1\2};UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250"))));SUMIFS(TEST!$F:$F;TEST!$A:$A;"<="&$F$4;TEST!$D:$D;">"&$F$4;TEST!$D:$D;"<"&EDATE($F$4;1);TEST!$E:$E;"<>R.";TEST!$E:$E;"<>7250";TEST!$B:$B;UNIQUE(FILTER(TEST!$B:$B;((TEST!$D:$D>$F$4)*(TEST!$A:$A<=$F$4)*(TEST!$D:$D<EDATE($F$4;1))*(TEST!$E:$E<>"R.")*(TEST!$E:$E<>"7250"))))));2;-1);SEQUENCE(5;1;1);{1\2});"-")

My results obtained are as following:

What I would want to obtain is (I did the table by summing the values manually - I would need it automatic)

I copy the test table in case it is useful

DATE (A)	CASE (B)	CHECK (C)	SOLUTION (D)	TYPE (E)	VALUE (F)
21/05/2021	P	DONE	28/05/2021		4,85
21/05/2021	P	DONE	02/06/2021	R.	8.893,96
22/05/2021	CA	MISSING			400.000,00
22/05/2021	P	DONE	28/05/2021		1,67
22/05/2021	CS	DONE	02/06/2021	R.	355.791,00
22/05/2021	CS	DONE	02/06/2021	R.	358,64
22/05/2021	CS	DONE	02/06/2021	R.	358,64
22/05/2021	CS	DONE	02/06/2021	R.	355.791,00
22/05/2021	CA	DONE	15/06/2021		400.000,00
25/05/2021	CO	DONE	28/05/2021		100.000,00
25/05/2021	P	DONE	01/06/2021		5,25
25/05/2021	F	DONE	02/06/2021	R.	7.300.000,00
26/05/2021	CA	DONE	16/06/2021		1.718.000,00
26/05/2021	CO	DONE	01/06/2021		422.832,98
26/05/2021	CO	DONE	02/06/2021	R.	1.086,77
26/05/2021	CO	DONE	02/06/2021	R.	1.086,77
26/05/2021	CO	DONE	02/06/2021	R.	983,74
26/05/2021	CO	DONE	03/06/2021	7250	9.244.707,41
26/05/2021	CO	DONE	03/06/2021	7250	8.336.807,00
26/05/2021	CA	DONE	15/06/2021		1.500.000,00
27/05/2021	CA	MISSING			480.000,00
27/05/2021	P	DONE	01/06/2021		15,75
27/05/2021	CA	DONE	02/06/2021	R.	4.363,23
27/05/2021	CA	DONE	02/06/2021	R.	4.363,23
27/05/2021	CA	DONE	15/06/2021		400.000,00
28/05/2021	PF	DONE	01/06/2021		0,97
28/05/2021	FV	DONE	02/06/2021	R.	714.000,00
28/05/2021	CA	DONE	15/06/2021		1.428.000,00

Thank you very much.

 

[Leticia]

Probably not without making it even more complicated.

mmm cool! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

Thinking about it, as you are only returning 5 rows you could use conditional formatting to show a - instead of a 0