Finding unique values with Criterias

Hi,

i used the formula

=SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH
(Range1,Range1,0),""),
IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))

and it worked! thanks...however, i need somehow change
the fomula such that it gives the number of unique
entries in range A if the equivalent value in B is
not "XI"....ie, in the example below, the answer I want
should be 3 (Alice and betty....alice should still be
counted...Candy shld be eliminated coz Candy appears
twice but twice the RangeB value is XI)

Is this possible?


RangeA RangeB
alice OK
Betty OK
Candy XI
Betty OK
alice XI
Denise OK
Candy XI

Thanks!
Val

Try...

=SUM(IF(FREQUENCY(IF((LEN(A1:A7)>0)*(B1:B7<>"XI"),MATCH(A1:A7,A1:A7,0)),I
F((LEN(A1:A7)>0)*(B1:B7<>"XI"),MATCH(A1:A7,A1:A7,0)))>0,1,0))

OR

=SUM(IF(FREQUENCY(IF((LEN(A1:A7)>0)*(B1:B7<>"XI"),MATCH(A1:A7,A1:A7,0)),R
OW(INDIRECT("1:"&ROWS(A1:A7))))>0,1,0))

Both of these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
"dolpphinv4" <[email protected]> wrote:

> Hi,
>
> i used the formula
>
> =SUM(IF(FREQUENCY(IF(LEN(Range1)>0,MATCH
> (Range1,Range1,0),""),
> IF(LEN(Range1)>0,MATCH(Range1,Range1,0),""))>0,1))
>
> and it worked! thanks...however, i need somehow change
> the fomula such that it gives the number of unique
> entries in range A if the equivalent value in B is
> not "XI"....ie, in the example below, the answer I want
> should be 3 (Alice and betty....alice should still be
> counted...Candy shld be eliminated coz Candy appears
> twice but twice the RangeB value is XI)
>
> Is this possible?
>
>
> RangeA RangeB
> alice OK
> Betty OK
> Candy XI
> Betty OK
> alice XI
> Denise OK
> Candy XI
>
> Thanks!
> Val