Conditional Unique Record 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". Is it possible to type a formula into one cell that will provide this 'Conditional' unique record count?
Your attention to this problem is greatly appreciated.

 

[fairwinds]

Welcome!

I was sure it could be done shorter, and maybe with sumproduct. But after trying and trying I give up and give you this formula that has to be entered with Ctrl + Shift + Enter.

=COUNTIF(B1:B10,"RED")-SUM((A1:A10&B1:B10=TRANSPOSE(A1:A10&B1:B10))*(B1:B10="RED"))/2+COUNTIF(B1:B10,"RED")/2

 

[Aladin Akyurek]

With the morefunc.xll add-in installed and D1 housing the condition Red:

=COUNTDIFF(IF((B2:B10=D1)*(A2:A10<>""),A2:A10))-1

Otherwise:

=SUM(IF(FREQUENCY(IF((B1:B10=D1)*(A1:A10<>""),MATCH(A1:A10,A1:A10,0),""),IF((B1:B10=D1)*(A1:A10<>""),MATCH(A1:A10,A1:A10,0),""))>0,1))

Each of these formulas must be confirmed with control+shift+enter instead of just with enter.

 

[indiantrix]

This may be a simpler solution?
Let us suppose we want to count the unique values in the range A1:A50. We can ARRAY enter the following formula, =Sum(1/Countif(A1:A50,A1:A50)). Hope this helps. /s/ Larry