Results within results

[gberg]

I have a table that I want to count the unique values based on; "Year-Month", "Type" and "District". Within those results I want to only count the unique "Job Numbers".

This table is a simplified version of the larger table. The "Year-Month", "Type" and "District" all meet the criteria I want, in the real table these values will not always match.

Year - Month	Job Number	Type	District
2024 - 03 March	71D0097	CON	71
2024 - 01 January	71D0099	CON	71
2024 - 01 January	71D0099	CON	71
2024 - 01 January	71D0102	CON	71
2024 - 04 April	71D0102	CON	71
2024 - 01 January	71D0103	CON	71
2024 - 01 January	71D0105	CON	71
2024 - 01 January	71D0106	CON	71
2024 - 01 January	71D0106	CON	71
2024 - 01 January	71D0107	CON	71
2024 - 01 January	71D0107	CON	71
2024 - 01 January	71D0108	CON	71
2024 - 04 April	71D0108	CON	71
	13​
Unique Job #'s	7​

All 13 items match the criteria for "Year" = 2024, "Type" = CON and "District" = 71, I have the following formula to look for this combination:

SUMPRODUCT(--(C2:C14="CON"),--(TEXT(D2:D14,0)="71"),--(LEFT(A2:A14,4)="2024"))

Within these results there are 7 unique Job Numbers. I want to have a formula that looks for the "Year", "Type" and "District" but then looks for the unique Job Numbers within these results. I would need this all as one formula. So the final value I am looking for would be 7 from the table above

 

[hagia_sofia]

How about:

=ROWS(UNIQUE(FILTER(B2:B14,IFERROR(MAP(A2:A14,C2:C14,D2:D14,LAMBDA(a,b,c,AND(SEARCH(2024,a),b="CON",c=71))),FALSE)=TRUE)))

It seems there are 8 unique IDs based on your criteria...

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F
1	Year - Month	Job Number	Type	District
2	2024 - 03 March	71D0097	CON	71		8
3	2024 - 01 January	71D0099	CON	71
4	2024 - 01 January	71D0099	CON	71
5	2024 - 01 January	71D0102	CON	71
6	2024 - 04 April	71D0102	CON	71
7	2024 - 01 January	71D0103	CON	71
8	2024 - 01 January	71D0105	CON	71
9	2024 - 01 January	71D0106	CON	71
10	2024 - 01 January	71D0106	CON	71
11	2024 - 01 January	71D0107	CON	71
12	2024 - 01 January	71D0107	CON	71
13	2024 - 01 January	71D0108	CON	71
14	2024 - 04 April	71D0108	CON	71
Sheet6

Cell Formulas
Range	Formula
F2	F2	=ROWS(UNIQUE(FILTER(B2:B100,(LEFT(A2:A100,4)="2024")*(C2:C100="con")*(D2:D100=71))))

 

[gberg]

Thank you Fluff! That works great.

 

[Fluff]

Glad we could help & thanks for the feedback.