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 ,
"dolpphinv4" 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