CODEWITHSUNDEEP
excelexcel-formula
kit99
kit99

Reputation: 187

Excel formula to look for several conditions in two columns

Got 4000 rows With Serial numbers in column N and status codes in column H. Both numbers and codes reappear several times in these rows.

I'm trying to Write a formula to check if Serial number at "this row" is present somwhere else in column N - AND determine if status code "BK" or "BE" is present in column H - at the same time as no one of status codes "TR"/"MO" should be present in column H. But I can't get my formula to work properly (states "Yes" when it should be "No")... This is my latest attempt (for row 1103):

=IF(AND(N:N=N1103,OR(H:H="BE",H:H="BK"),AND(OR(H:H<>"MO",H:H<>"TR"))),"Yes","No")

Anyone that can help me out with a working formula for this?

Upvotes: 0

Views: 176

Answers (1)

JvdV
JvdV

Reputation: 75840

You could use a SUMPRODUCT() variant:

=IF(AND(SUMPRODUCT(($N$1:$N$4000=N1)*($H$1:$H$4000={"BK";"BE"}))>0,SUMPRODUCT(($N$1:$N$4000=N1)*($H$1:$H$4000={"TR";"MO"}))=0),"Yes","No")

And to go with your approach, it would probably look like:

=IF(AND(SUM(COUNTIFS($H$1:$H$4000,{"BK","BE"},$N$1:$N$4000,N1))>0,SUM(COUNTIFS($H$1:$H$4000,{"MO","TR"},$N$1:$N$4000,N1))=0),"Yes","No")

enter image description here

Note, this does include the current row's values in H and N as well.

Upvotes: 1

Related Questions