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:

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

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.