Counting Unique Part Numbers In A Range

B

BigH

Hi,

Hopefully someone can help, in column A i have over 1000 part numbers (some
of which are duplicated many times) and in column B i have values ranging
from 1 to 320 which correspond to column A part numbers. The header in
column A is Part Numbers and the header in column B is Days Late.
What i want to calculate is how many unique part numbers are greater
than 10 days.

hope this makes sense

regards Big H
 
B

Biff

Hi!

Try this:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=SUM(N(FREQUENCY(IF(B1:B1000>10,MATCH(A1:A1000,A1:A1000,0)),MATCH(A1:A1000,A1:A1000,0))>0))

Biff
 
D

Domenic

If Column A contains numerical values, try...

=SUM(IF(FREQUENCY(IF(B2:B10>10,A2:A10),IF(B2:B10>10,A2:A10))>0,1))

If Column A contains alpha-numeric values, try...

=SUM(IF(FREQUENCY(IF((A2:A10<>"")*(B2:B10>10),MATCH(A2:A10,A2:A10,0)),ROW
(A2:A10)-ROW(A2)-1)>0,1))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER. Adjust the range accordingly.

Hope this helps!
 
Top