Conditional Unique Number Count

[Russel]

The following array formula counts the number of unique
values found in the range A1:A10 (excluding blank cells
and text enries):
SUM(IF(FREQUENCY(A1:A10,A1:A10)>0,1))
Is it possible to add an additional condition to this
formula in order to return the unique records for the
specified condition only?
An example to further clarify:
Job.No Colour
1055 Red
1066 Blue
1055 Red
1077 Red
1088 Green
The answer required is 2, as there are 2 unique Job.No's
for the Colour "red".
Your attention to this problem is greatly appreciated.

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER)
=SUM(IF(FREQUENCY(IF((B1:B10="Red"),MATCH(A1:A10,A1:A10,0),""),IF((B1:B
10="Red"),MATCH(A1:A10,A1:A10,0),""))>0,1))

 

[Andy B]

Hi

Have a look here:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

Andy