unique values among duplicates without considering specific words

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)
 
T

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top