unique and conditional count
 

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!

unique and conditional count
 

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

--
Biff
Microsoft Excel MVP


"rjreilly" wrote in message
...
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!

unique and conditional count
 

Perfect. Thanks again.

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"rjreilly" wrote in message
...
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!

unique and conditional count
 

You're welcome!

--
Biff
Microsoft Excel MVP


"rjreilly" wrote in message
...
Perfect. Thanks again.

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"rjreilly" wrote in message
...
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!