Return top x based on criteria

[rushford1]

I need the sheet "Top 5" to list the top 5 records from the sheet named in cell C2. The returned top 5 records should be based on the "Amount" (column B) and include only the records that fall into the fiscal quarter mentioned in cell C3 on the sheet "Top 5". If there are duplicate "Amount" values, they should be listed in the order they appear on the second sheet.

Is this possible? Thanks in advance!

 

[Eric W]

Try:

Book1
	A	B	C
1
2		Sheet Name	23Aug2024
3		Fiscal Year and Quarter	2024 Q3
4
5
6	Top #	Company	Amount
7	1	company E	500000
8	2	company F	60000
9	3	company C	48000
10	4	company A	7059
11	5
12
Top 5

Cell Formulas
Range	Formula
B7:C10	B7	=LET(table,INDIRECT("'"&C2&"'!A6:C100"),date,INDEX(table,0,1),year,LEFT(C3,4)+0,month,RIGHT(C3,1)*3-2,ty,YEAR(date),tm,MONTH(date),f,FILTER(table,(ty=year)*(tm>=month)*(tm<=month+2)),s,SORT(f,2,-1),INDEX(s,SEQUENCE(MIN(5,ROWS(s))),{3,2}))
Dynamic array formulas.

This assumes your fiscal quarters are the same as the yearly quarters, e.g. Q3 is July, August, September. This also assumes that the number of rows on your data sheets only go down to row 100, but that is easily changed, change the C100 to Cxxx where xxx is the bottommost possible row.

 

[rushford1]

Try:

Book1
	A	B	C
1
2		Sheet Name	23Aug2024
3		Fiscal Year and Quarter	2024 Q3
4
5
6	Top #	Company	Amount
7	1	company E	500000
8	2	company F	60000
9	3	company C	48000
10	4	company A	7059
11	5
12
Top 5

Cell Formulas
Range	Formula
B7:C10	B7	=LET(table,INDIRECT("'"&C2&"'!A6:C100"),date,INDEX(table,0,1),year,LEFT(C3,4)+0,month,RIGHT(C3,1)*3-2,ty,YEAR(date),tm,MONTH(date),f,FILTER(table,(ty=year)*(tm>=month)*(tm<=month+2)),s,SORT(f,2,-1),INDEX(s,SEQUENCE(MIN(5,ROWS(s))),{3,2}))
Dynamic array formulas.

This assumes your fiscal quarters are the same as the yearly quarters, e.g. Q3 is July, August, September. This also assumes that the number of rows on your data sheets only go down to row 100, but that is easily changed, change the C100 to Cxxx where xxx is the bottommost possible row.

Eric W - That is some crazy magic right there! THANK YOU SO MUCH!

 

[Eric W]

Happy to help!

 

[rushford1]

@Eric W - How would I get a total of the Amount (not filtered to the top 5) for the sheet mentioned in cell C2 and based on the fiscal quarter filter in cell C3 of the Top 5 sheet?
Robin

 

[Eric W]

With the data sheet like this:

Book1
	A	B	C
1
2
3
4
5	Date	Amount	Company
6	9/3/2024	$ 7,059.00	Company A
7	3/15/2024	$ 410,000.00	Company B
8	8/20/2024	$ 48,000.00	Company C
9	12/5/2024	$ 55,000.00	Company D
10	7/5/2024	$ 500,000.00	Company E
11	8/20/2024	$ 60,000.00	Company F
12	6/1/2024	$ 400,000.00	Company G
13	11/25/2024	$ 60,000.00	Company H
14	11/25/2024	$ 390,000.00	Company I
15	9/1/2025	$ 500,000.00	Company J
16
23Aug2024

You can get the total amount like this:

Book1
	A	B	C
1
2		Sheet Name	23Aug2024
3		Fiscal Year and Quarter	2024 Q3
4		Total Amount	$ 615,059.00
5
6	Top #	Company	Amount
7	1	Company E	$500,000.00
8	2	Company F	$ 60,000.00
9	3	Company C	$ 48,000.00
10	4	Company A	$ 7,059.00
11	5
12
Top 5

Cell Formulas
Range	Formula
C4	C4	=LET(sc,INDIRECT("'"&C2&"'!B6:B100"),dc,INDIRECT("'"&C2&"'!A6:A100"),sd,DATE(LEFT(C3,4),RIGHT(C3,1)*3-2,1),ed,EOMONTH(sd,2),SUMIFS(sc,dc,">="&sd,dc,"<="&ed))
B7:C10	B7	=LET(table,INDIRECT("'"&C2&"'!A6:C100"),date,INDEX(table,0,1),year,LEFT(C3,4)+0,month,RIGHT(C3,1)*3-2,ty,YEAR(date),tm,MONTH(date),f,FILTER(table,(ty=year)*(tm>=month)*(tm<=month+2)),s,SORT(f,2,-1),INDEX(s,SEQUENCE(MIN(5,ROWS(s))),{3,2}))
Dynamic array formulas.

Same caveat as before, change the bottom maximum row from 100 to whatever it really is.