Conditional Unique Number Count

  • Thread starter Thread starter Russel
  • Start date Start date
R

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.
 
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))
 
Back
Top