unique values among duplicates without considering specific words

  • Thread starter Thread starter Simna
  • Start date Start date
S

Simna

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)
 
Assuming there are no empty cells in column A.

Try this array formula** :

=SUM(IF(FREQUENCY(IF(B1:B4<>"del",MATCH(A1:A4,A1:A4,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.
 
Back
Top