unique and conditional count

[rjreilly]

the following sumproduct formula gives me a count of records meeting 2
conditions

=SUMPRODUCT(--(ISBLANK(Data!U2:U10000)=FALSE),--(ISBLANK(Data!C2:C10000)=TRUE))

My struggle is there are duplicate values in array Data!U2:U10000 and I need
a count ofunique items in Data!U2:U10000 which meeting the above conditions

Thanks in advance!

 

[T. Valko]

Try this array formula** :

C2:C10000 = rng1
U2:U10000 = rng2

=COUNT(1/FREQUENCY(IF(rng1="",IF(rng2<>"",MATCH(rng2,rng2,0))),ROW(rng2)-MIN(ROW(rng2))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Expect this to be slow on such a large range

 

[rjreilly]

Perfect. Thanks again.

Try this array formula** :

C2:C10000 = rng1
U2:U10000 = rng2

=COUNT(1/FREQUENCY(IF(rng1="",IF(rng2<>"",MATCH(rng2,rng2,0))),ROW(rng2)-MIN(ROW(rng2))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Expect this to be slow on such a large range

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Perfect. Thanks again.