Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions | |||
Finding Unique Values in Column | Excel Discussion (Misc queries) | |||
Count number of Unique values | Excel Worksheet Functions | |||
How do I get unique values from 2 columns? | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions |