unique values among duplicates without considering specific words
 

unique values among duplicates without considering specific words from
different columns.
Ex; column A Column B
1 abc del
2 abc
3 abc
4 bcd del
shud result in 1 (i.e; does not count the data which has del in column B,
also no duplicates in Column A)

unique values among duplicates without considering specific words
 

Assuming there are no empty cells in column A.

Try this array formula** :

=SUM(IF(FREQUENCY(IF(B1:B4<"del",MATCH(A1:A4,A1:A 4,0)),ROW(A1:A4)-ROW(A1)+1),1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Simna" wrote in message
...
unique values among duplicates without considering specific words from
different columns.
Ex; column A Column B
1 abc del
2 abc
3 abc
4 bcd del
shud result in 1 (i.e; does not count the data which has del in column B,
also no duplicates in Column A)