# Thread: How to create a conditional statement with multiple values in excel

1. ## How to create a conditional statement with multiple values in excel

 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!

2. Try this

=IF(COUNTIF(A2:D2,"NAP")=4,"NAP",IF(SUM(COUNTIF(A2:D2,{"Yes","NAP"}))=4,"Yes","No"))

3. ## Thank you!!!!

Originally Posted by Bob Phillips
Try this

=IF(COUNTIF(A22,"NAP")=4,"NAP",IF(SUM(COUNTIF(A22,{"Yes","NAP"}))=4,"Yes","No"))
YOU ROCK

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•