Count distinct values in column X based on unique value in col Y

[creative999]

I want to count distinct values based in col B but this needs to be based on unique values in col A.

Example below.

example.xlsx
	A	B	C
1	PROD	PRODID
2	RESALE	ABC
3	RESALE	ABC
4	RESALE	ABC	ABC = 1
5	RESALE	DEF
6	RESALE	DEF
7	RESALE	DEF	DEF = 1
8	RESALE	IJK	IJK = 1
9	MANU	IJK
10	MANU	DEF	DEF - 1
11	MANU	IJK
12	MANU	IJK
13	MANU	IJK	IJK = 1
14	DEL	ABC
15	DEL	ABC
16	DEL	ABC
17	DEL	ABC
18	DEL	ABC	ABC = 1
19	DEL	DEF
20	DEL	DEF
21	DEL	DEF
22	DEL	DEF	DEF = 1
23	DEL	IJK	IJK = 1
24	RESALE 2	DEF
25	RESALE 2	DEF
26	RESALE 2	DEF
27	RESALE 2	DEF
28	RESALE 2	DEF
29	RESALE 2	DEF	DEF = 1
30	RESALE 2	ABC
31	RESALE 2	ABC
32	RESALE 2	ABC	ABC = 1
33
34
35		TOTALS	ABC = 3
36			DEF = 4
37			IJK.= 3
report1675758552260

 

[JvdV]

Here is one way:

Book1
	A	B	C	D	E
1	PROD	PRODID
2	RESALE	ABC		ABC	3
3	RESALE	ABC		DEF	4
4	RESALE	ABC		IJK	3
5	RESALE	DEF
6	RESALE	DEF
7	RESALE	DEF
8	RESALE	IJK
9	MANU	IJK
10	MANU	DEF
11	MANU	IJK
12	MANU	IJK
13	MANU	IJK
14	DEL	ABC
15	DEL	ABC
16	DEL	ABC
17	DEL	ABC
18	DEL	ABC
19	DEL	DEF
20	DEL	DEF
21	DEL	DEF
22	DEL	DEF
23	DEL	IJK
24	RESALE 2	DEF
25	RESALE 2	DEF
26	RESALE 2	DEF
27	RESALE 2	DEF
28	RESALE 2	DEF
29	RESALE 2	DEF
30	RESALE 2	ABC
31	RESALE 2	ABC
32	RESALE 2	ABC
Sheet4

Cell Formulas
Range	Formula
D2:E4	D2	=LET(x,UNIQUE(B2:B32),HSTACK(x,MAP(x,LAMBDA(y,IFERROR(ROWS(UNIQUE(FILTER(A2:A32,B2:B32=y))),0)))))
Dynamic array formulas.

 

[creative999]

Works a treat.
How would i expand this if I wanted an extra condition, ie count only with a status of Closed:

example.xlsx
	A	B	C	D
1	PROD	PRODID	STATUS
2	RESALE	ABC	OPEN
3	RESALE	ABC	OPEN
4	RESALE	ABC	OPEN	ABC = 1
5	RESALE	DEF	OPEN
6	RESALE	DEF	OPEN
7	RESALE	DEF	OPEN	DEF = 1
8	RESALE	IJK	OPEN	IJK = 1
9	MANU	IJK	CLOSED
10	MANU	DEF	CLOSED	DEF - 1
11	MANU	IJK	CLOSED
12	MANU	IJK	CLOSED
13	MANU	IJK	CLOSED	IJK = 1
14	DEL	ABC	IN PROGRESS
15	DEL	ABC	IN PROGRESS
16	DEL	ABC	IN PROGRESS
17	DEL	ABC	IN PROGRESS
18	DEL	ABC	IN PROGRESS	ABC = 1
19	DEL	DEF	IN PROGRESS
20	DEL	DEF	IN PROGRESS
21	DEL	DEF	IN PROGRESS
22	DEL	DEF	IN PROGRESS	DEF = 1
23	DEL	IJK	IN PROGRESS	IJK = 1
24	RESALE 2	DEF	OPEN
25	RESALE 2	DEF	OPEN
26	RESALE 2	DEF	OPEN
27	RESALE 2	DEF	OPEN
28	RESALE 2	DEF	OPEN
29	RESALE 2	DEF	OPEN	DEF = 1
30	RESALE 2	ABC	OPEN
31	RESALE 2	ABC	OPEN
32	RESALE 2	ABC	OPEN	ABC = 1
report1675758552260

 

[JvdV]

Answer_Reddit.xlsx
	A	B	C	D	E
1	PROD	PRODID	STATUS	ABC	0
2	RESALE	ABC	OPEN	DEF	1
3	RESALE	ABC	OPEN	IJK	1
4	RESALE	ABC	OPEN
5	RESALE	DEF	OPEN
6	RESALE	DEF	OPEN
7	RESALE	DEF	OPEN
8	RESALE	IJK	OPEN
9	MANU	IJK	CLOSED
10	MANU	DEF	CLOSED
11	MANU	IJK	CLOSED
12	MANU	IJK	CLOSED
13	MANU	IJK	CLOSED
14	DEL	ABC	IN PROGRESS
15	DEL	ABC	IN PROGRESS
16	DEL	ABC	IN PROGRESS
17	DEL	ABC	IN PROGRESS
18	DEL	ABC	IN PROGRESS
19	DEL	DEF	IN PROGRESS
20	DEL	DEF	IN PROGRESS
21	DEL	DEF	IN PROGRESS
22	DEL	DEF	IN PROGRESS
23	DEL	IJK	IN PROGRESS
24	RESALE 2	DEF	OPEN
25	RESALE 2	DEF	OPEN
26	RESALE 2	DEF	OPEN
27	RESALE 2	DEF	OPEN
28	RESALE 2	DEF	OPEN
29	RESALE 2	DEF	OPEN
30	RESALE 2	ABC	OPEN
31	RESALE 2	ABC	OPEN
32	RESALE 2	ABC	OPEN
Sheet6

Cell Formulas
Range	Formula
D1:E3	D1	=LET(x,UNIQUE(B2:B32),HSTACK(x,MAP(x,LAMBDA(y,IFERROR(ROWS(UNIQUE(FILTER(A2:A32,(B2:B32=y)*(C2:C32="Closed")))),0)))))
Dynamic array formulas.