Unique Total Value Count per Unique Lookup Values

[KnightVision]

Hello all, I have a perplexing problem that I cannot solve, and would like to know if anyone could help. My sample data:

	A	B	C	D	E	F	G	H	I	J	K
1	Raw Data
2	WO #	Op #	Serial #	Program #	NC Info	# of Kickout	Kickout Types	Comments	Unique WO with NC Pending	Unique Serials (per WO) with No NC - INCORRECT ANSWER	Unique Serials (per WO) with No NC - DESIRED ANSWER
3	WO-A	10	14a, 14b	2100	123000	4	P	Any Text	1	6	8
4	WO-B	20	15a, 15b	2100	123002	3	C	Any Text
5	WO-C	20	16c	2200	NC Pending	2	S	Any Text
6	WO-D	30	17d	2300	Need NC	1	C	Any Text
7	1010	20	700	2100	No NC	0		Any Text
8	1010	20	720	2100	No NC	0		Any Text
9	1010	30	740	2200, 2300	No NC	0		Any Text
10	1020	20	Ser-01	2100	No NC	0		Any Text
11	1020	10	Ser-01	2200	No NC	0		Any Text
12	1020	30	Ser-02	2100	No NC	0		Any Text
13	1030	10	500	2300	No NC	0		Any Text
14	1030	30	500	2400	No NC	0		Any Text
15	1030	30	500	2500	No NC	0		Any Text
16	1010	30	720	2400	No NC	0		Any Text
17	1010	40	740	2500	No NC	0		Any Text
18	1010	10	Ser-01	2300	No NC	0		Any Text
19	1010	20	Ser-01	2400	No NC	0		Any Text
20	1010	20	Ser-01	2500	No NC	0		Any Text
21	1020	20	500	2100	No NC	0		Any Text

<tbody>
</tbody>

GIVEN:
Each WO can have multiple Serials. These Serials can be unique, repeated, or both (for a certain WO). Serials may also repeat across different WOs.

There are four categories for NC Info (a number, "NC Pending", "Need NC", and "No NC").

FIND:
Count the total number of unique Serials PER WO that meet the criteria of "No NC" in the NC Info column.

EXAMPLE:
• WO 1010 is listed 8 times in the WO # column.
• There are four different serials (700, 720, 740, Ser-01) for WO 1010.
• Serial 700 does not repeat FOR THIS UNIQUE WO. Serials 720, 740, and Ser-01 repeat FOR THIS UNIQUE WO.
• Thus, the number of unique Serials in WO 1010 that have the NC Info of "No NC" is 4. Notice that Ser-01 also shows up for WO 1020 twice. So Ser-01 will be counted once for WO 1010, and once for WO 1020.

I need to know the number of unique Serials (per WO) in total that meet the “No NC” criteria. Because some Serials may appear in more than one WO (where they may also repeat), the count for that Serial may or may not equal 1, but may increase if the Serial is in different WOs.

MORE INFO:
I have managed to use SUM(IF(FREQUENCY(IF… to successfully find the number of unique WOs that meet other NC Info categories, such as those with a number or those with “NC Pending”. Note that I was searching the WOs and not Serials. For example, to find all of the “NC Pending” WOs, I used:

{=SUM(IF(FREQUENCY(IF($E$3:$E$500="nc pending",MATCH($A$3:$A$500,$A$3:$A$500,0)),IF($E$3:$E$500="nc pending",MATCH($A$3:$A$500,$A$3:$A$500,0)))>0,1))}

Which returns the correct answer of 1 for my sample data.

The “No NC” scenario is different. I now want to search the Serials. I have tried the following formula in cell J3 but it does not give the correct answer.

{=SUM(IF(FREQUENCY(IF($E$3:$E$500="no nc",MATCH($C$3:$C$500, $C$3:$C$500,0)),IF($E$3:$E$500="no nc",MATCH($C$3:$C$500,$C$3:$C$500,0)))>0,1))}

The result of this formula is 6. As you can tell from my sample data, the correct result should be 8 (4 unique Serials for WO 1010, 3 unique Serials for WO 1020, and 1 unique Serial for WO 1030).

Thank you for your time and I would appreciate any help you can provide.

 

[Aladin Akyurek]

1. Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$3:$A$21<>"",IF($E$3:$E$21="NC Pending",
  MATCH("~"&$A$3:$A$21,$A$3:$A$21&"",0))),ROW($A$3:$A$21)-ROW($A$3)+1),1))

2. Control=shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($C$3:$C$21<>"",IF($E$3:$E$21="No NC",
  MATCH($C$3:$C$21&"|"&$A$3:$A$21,$C$3:$C$21&"|"&$A$3:$A$21,0))),
  ROW($C$3:$C$21)-ROW($C$3)+1),1))

 

[KnightVision]

These two formulas work perfectly, thanks very much! It is exactly what I needed.

 

[Aladin Akyurek]

These two formulas work perfectly, thanks very much! It is exactly what I needed.

You are welcome. Thanks for providing feedback.