Assistance with finding top 2 repeated values based on 2 criteria (LET,UNIQUE,FILTER,TAKE,SORT,HSTACK,COUNTIF formula)

[willow1985]

I am looking to pull the top 2 repeated part numbers based criteria from 2 other columns: Division and Yes/No (for a date range).

If I filter the data by Green Co. and Date Range "Yes", the top 2 part numbers are AXT455 and N21W83 by 3 and 2, however the formula I wrote is counting as a whole, not based on the criteria.
How can I fix this formula?

=LET(u,UNIQUE(FILTER(Table1[Part '#],(Table1[Falls Between Date Range]="Yes")*(Table1[Division]=$J$2))),TAKE(SORT(HSTACK(u,COUNTIF(Table1[Part '#],u)),2,-1),2))

Thank you to anyone who can help!

test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Part #	Return Date	Division	Falls Between Date Range		Top 2 Returned Part #'s			Date Range:	January 1, 2024	To	February 28, 2024
2	N52T17	21-Nov-23	Blue Co.	No		AXT455	7		Division	Green Co.
3	AXT455	22-Apr-24	Green Co.	No		N21X83	2
4	AXT455	28-Feb-24	Green Co.	Yes
5	N21X83	17-Jan-24	Green Co.	Yes
6	N21X52	2-Aug-23	Blue Co.	No		Expected Answer:
7	AXT455	29-Aug-23	Red Inc.	No		AXT455	3
8						N21X83	2
9
10
11	N52T25	29-Feb-24	Blue Co.	No
12	AXT455	10-Jan-24	Green Co.	Yes
13	AXT455	23-Oct-23	Red Inc.	No
14	N52T17	22-May-24	Red Inc.	No
15	N52T25	10-Jan-24	Blue Co.	Yes
16
17	N21X83	15-Jan-24	Green Co.	Yes
18	N21X52	5-Dec-23	Blue Co.	No
19	T58X17	22-May-24	Green Co.	No
20	AXT455	5-Jan-24	Blue Co.	Yes
21	N21X52	20-Feb-24	Red Inc.	Yes
22	AXT455	22-Feb-24	Green Co.	Yes
Sheet1

Cell Formulas
Range	Formula
F2:G3	F2	=LET(u,UNIQUE(FILTER(Table1[Part '#],(Table1[Falls Between Date Range]="Yes")*(Table1[Division]=$J$2))),TAKE(SORT(HSTACK(u,COUNTIF(Table1[Part '#],u)),2,-1),2))
D2:D7,D17:D22,D11:D15	D2	=IF(AND(B2>=$J$1,B2<=$L$1),"Yes","No")
Dynamic array formulas.

 

[willow1985]

I believe I figured it out. Should use Countifs...

If anyone can provide feedback if this is the correct approach I would appreciate it!

=LET(u,UNIQUE(FILTER(Table1[Part '#],(Table1[Falls Between Date Range]="Yes")*(Table1[Division]=$J$2))),TAKE(SORT(HSTACK(u,COUNTIFS(Table1[Part '#],u,Table1[Division],$J$2,Table1[Falls Between Date Range],"Yes")),2,-1),2))

 

[alansidman]

An alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Falls Between Date Range] = "Yes")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Part #", "Division"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Part #	Return Date	Division	Falls Between Date Range		Top 2 Returned Part #'s			Date Range:	1/1/2024	To	2/28/2024
2	N52T17	11/21/2023	Blue Co.	No					Division	Green Co.
3	AXT455	4/22/2024	Green Co.	No
4	AXT455	2/28/2024	Green Co.	Yes
5	AXT455	1/10/2024	Green Co.	Yes
6	N21X52	8/2/2023	Blue Co.	No		Expected Answer:
7	AXT455	8/29/2023	Red Inc.	No		AXT455	3
8						N21X83	2
9
10
11	N52T25	2/29/2024	Blue Co.	No
12	AXT455	1/5/2024	Blue Co.	Yes
13	AXT455	10/23/2023	Red Inc.	No
14	N52T17	5/22/2024	Red Inc.	No
15	AXT455	2/22/2024	Green Co.	Yes
16
17	N21X52	2/20/2024	Red Inc.	Yes
18	N21X52	12/5/2023	Blue Co.	No
19	T58X17	5/22/2024	Green Co.	No
20	N21X83	1/17/2024	Green Co.	Yes
21	N21X83	1/15/2024	Green Co.	Yes
22	N52T25	1/10/2024	Blue Co.	Yes
Sheet4

Cell Formulas
Range	Formula
D2:D7,D17:D22,D11:D15	D2	=IF(AND(B2>=$J$1,B2<=$L$1),"Yes","No")
F7:G8	F7	=LET( u,UNIQUE(TOCOL(Table1[Part '#],1)), c,COUNTIFS(Table1[Part '#],u,Table1[Falls Between Date Range],"Yes",Table1[Division],J2), TAKE(SORTBY(HSTACK(u,c),c,-1),2))
Dynamic array formulas.