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)