How to create a conditional statement with multiple values in excel

T

TyT

New member
Joined
May 29, 2014
Messages
4
Reaction score
0
Points
0


I have four columns in excel that will either hold the value "Yes", "No" or "NAP" I need to write a formula in a 5th column that will display "Yes" if at least 1 out of 4 of the values = "Yes" and the other 3 = "NAP". The 5th column would also = "Yes" if it were two Yes's and two NAP's or three Yes's and 1 NAP, etc.


If all four columns = NAP than the 5th column needs to display "NAP".


If any of the four columns = "No" than the 5th column has to = "No".



Here is what I have so far...

=IF(COUNTIF(BR4:BU4,"No")>0,"No",IF(COUNTIF(BR4:BU4,"NAP")=4,"NAP","Yes"))

The only problem with this formula is that it forces my "5th column" to start with the value of "Yes" which is unacceptable. Can anyone help me figure out how to achieve the results of my formula but have my 5th column display "No" if the other four columns are blank?

Thanks!




 
Try this

=IF(COUNTIF(A2:D2,"NAP")=4,"NAP",IF(SUM(COUNTIF(A2:D2,{"Yes","NAP"}))=4,"Yes","No"))
 
Last edited:
You must log in or register to reply here.
Back
Top