Find top total values with conditions, and tie

[Habtest]

Hello, I've tried for a while but am not going anywhere.

I'd like that, when year and month are entered in G2 and G3, a top-3 list be generated starting from F6, based on data in column A-D.

The list should contain at least 3 people, when a tie compels expanding the list, expand then conclude the list (scenario 2)

It'd be best not to have helper cell/column.

Appreciate any help!

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Date	Name	Score		Scenario 1
2	KZS033	3/28/2021	Em	180		Year	2021
3	KZS033	3/28/2021	Em	100		Month	3
4	KZI092	3/29/2021	Dan	150
5	KZR009	3/29/2021	Barry	50		#	ID	Name	Score
6	KZI092	3/29/2021	Dan	180		1	KZI092	Dan	500
7	KZL094	3/30/2021	Charlie	180		1	KZS033	Em	500
8	KZS033	3/30/2021	Em	40		3	KZL094	Charlie	180
9	KZS033	3/31/2021	Em	180
10	KZR009	3/31/2021	Barry	100
11	KZI092	3/31/2021	Dan	160		Scenario 2
12	KZI092	3/31/2021	Dan	10		Year	2021
13	KZS033	4/1/2021	Em	150		Month	4
14	KZS033	4/2/2021	Em	120
15	KZR009	4/2/2021	Barry	30		#	ID	Name	Score
16	KZI092	4/3/2021	Dan	80		1	KZS033	Em	270
17	KZX066	4/3/2021	Amy	10		2	KZR009	Barry	160
18	KZX066	4/4/2021	Amy	20		2	KZL094	Charlie	160
19	KZI092	4/4/2021	Dan	80		2	KZI092	Dan	160
20	KZR009	4/5/2021	Barry	130
21	KZX066	4/5/2021	Amy	80
22	KZL094	4/6/2021	Charlie	160
Sheet1

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	ID	Date	Name	Score
2	KZS033	28/03/2021	Em	180		Year	2021
3	KZS033	28/03/2021	Em	100		Month	3
4	KZI092	29/03/2021	Dan	150
5	KZR009	29/03/2021	Barry	50		#	ID	Name	Score
6	KZI092	29/03/2021	Dan	180		1	KZS033	Em	500
7	KZL094	30/03/2021	Charlie	180		1	KZI092	Dan	500
8	KZS033	30/03/2021	Em	40		3	KZL094	Charlie	180
9	KZS033	31/03/2021	Em	180
10	KZR009	31/03/2021	Barry	100
11	KZI092	31/03/2021	Dan	160
12	KZI092	31/03/2021	Dan	10
13	KZS033	01/04/2021	Em	150
14	KZS033	02/04/2021	Em	120
15	KZR009	02/04/2021	Barry	30
16	KZI092	03/04/2021	Dan	80
17	KZX066	03/04/2021	Amy	10
18	KZX066	04/04/2021	Amy	20
19	KZI092	04/04/2021	Dan	80
20	KZR009	05/04/2021	Barry	130
21	KZX066	05/04/2021	Amy	80
22	KZL094	06/04/2021	Charlie	160
23
Master

Cell Formulas
Range	Formula
F6:I8	F6	=LET(Id,UNIQUE(A2:A22),Tot,SUMIFS(D2:D22,B2:B22,">="&DATE(G2,G3,1),B2:B22,"<"&DATE(G2,G3+1,1),A2:A22,Id),Rnk,MATCH((SORT(Tot,,-1)),Tot,0),Srt,SORT(CHOOSE(SEQUENCE(,4),Rnk,Id,XLOOKUP(Id,A2:A22,C2:C22,0),Tot),3,-1),FILTER(Srt,INDEX(Srt,,1)<=3))
Dynamic array formulas.

 

[Marcelo Branco]

Another alternative is to use a PivotTable (often overlooked, but still useful ... )

With some adjustments (no Subtotal; grouping Dates), a Slicer (Date), Top 3 etc...
I got

Scenario1

Pasta1
	A	B	C	D
1
2
3	ID	Name	Date	Score
4	KZI092	Dan	mar	500
5	KZS033	Em	mar	500
6	KZL094	Charlie	mar	180
7	Grand Total			1180
8
Plan5

Using the Slicer
Scenario 2

Pasta1
	A	B	C	D
1
2
3	ID	Name	Date	Score
4	KZS033	Em	abr	270
5	KZR009	Barry	abr	160
6	KZI092	Dan	abr	160
7	KZL094	Charlie	abr	160
8	Grand Total			750
Plan5

Hope this helps

M.

 

[Fluff]

Realised that there is a problem with the formula in post#2.
Use this instead

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	ID	Date	Name	Score
2	KZS033	28/03/2021	Em	180		Year	2021
3	KZS033	28/03/2021	Em	100		Month	3
4	KZI092	29/03/2021	Dan	150
5	KZR009	29/03/2021	Barry	50		#	ID	Name	Score
6	KZI092	29/03/2021	Dan	180		1	KZS033	Em	500
7	KZL094	30/03/2021	Charlie	180		1	KZI092	Dan	500
8	KZS033	30/03/2021	Em	40		3	KZL094	Charlie	180
9	KZS033	31/03/2021	Em	180
10	KZR009	31/03/2021	Barry	100
11	KZI092	31/03/2021	Dan	160
12	KZI092	31/03/2021	Dan	10
13	KZS033	01/04/2021	Em	150
14	KZS033	02/04/2021	Em	120
15	KZR009	02/04/2021	Barry	30
16	KZI092	03/04/2021	Dan	80
17	KZX066	03/04/2021	Amy	10
18	KZX066	04/04/2021	Amy	20
19	KZI092	04/04/2021	Dan	80
20	KZR009	05/04/2021	Barry	130
21	KZX066	05/04/2021	Amy	80
22	KZL094	06/04/2021	Charlie	160
23
Master

Cell Formulas
Range	Formula
F6:I8	F6	=LET(Id,UNIQUE(A2:A22),Tot,SUMIFS(D2:D22,B2:B22,">="&DATE(G2,G3,1),B2:B22,"<"&DATE(G2,G3+1,1),A2:A22,Id),Rnk,MATCH(Tot,(SORT(Tot,,-1)),0),Srt,SORT(CHOOSE(SEQUENCE(,4),Rnk,Id,XLOOKUP(Id,A2:A22,C2:C22,0),Tot),4,-1),FILTER(Srt,INDEX(Srt,,1)<=3))
Dynamic array formulas.

 

[Habtest]

Realised that there is a problem with the formula in post#2.
Use this instead

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	ID	Date	Name	Score
2	KZS033	28/03/2021	Em	180		Year	2021
3	KZS033	28/03/2021	Em	100		Month	3
4	KZI092	29/03/2021	Dan	150
5	KZR009	29/03/2021	Barry	50		#	ID	Name	Score
6	KZI092	29/03/2021	Dan	180		1	KZS033	Em	500
7	KZL094	30/03/2021	Charlie	180		1	KZI092	Dan	500
8	KZS033	30/03/2021	Em	40		3	KZL094	Charlie	180
9	KZS033	31/03/2021	Em	180
10	KZR009	31/03/2021	Barry	100
11	KZI092	31/03/2021	Dan	160
12	KZI092	31/03/2021	Dan	10
13	KZS033	01/04/2021	Em	150
14	KZS033	02/04/2021	Em	120
15	KZR009	02/04/2021	Barry	30
16	KZI092	03/04/2021	Dan	80
17	KZX066	03/04/2021	Amy	10
18	KZX066	04/04/2021	Amy	20
19	KZI092	04/04/2021	Dan	80
20	KZR009	05/04/2021	Barry	130
21	KZX066	05/04/2021	Amy	80
22	KZL094	06/04/2021	Charlie	160
23
Master

Cell Formulas
Range	Formula
F6:I8	F6	=LET(Id,UNIQUE(A2:A22),Tot,SUMIFS(D2:D22,B2:B22,">="&DATE(G2,G3,1),B2:B22,"<"&DATE(G2,G3+1,1),A2:A22,Id),Rnk,MATCH(Tot,(SORT(Tot,,-1)),0),Srt,SORT(CHOOSE(SEQUENCE(,4),Rnk,Id,XLOOKUP(Id,A2:A22,C2:C22,0),Tot),4,-1),FILTER(Srt,INDEX(Srt,,1)<=3))
Dynamic array formulas.

Thanks Fluff, works perfectly, and TIL something new with CHOOSE()

Thanks Marcelo, it also works, though this particular task doesn't allow much leeway for re-format, such as pivot table and helper cells.

 

[Fluff]

Glad we could help & thanks for the feedback.