# If and/or

L

#### Legacy 385836

##### Guest
Hi

I have a spreadsheet to check compliance. I have 3 columns, 2 of which are yes/no and the third is yes/no/N/A.
I need the final column to display No if any of the previous 3 columns show No, and to display yes if al the columns are yes or n/a.

I have tried to use an IF statement and nested IF statements with no luck.

Any help Would be much appreciated.

Thanks.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### PlusX EAI

##### New Member
Code:
``=IF(COUNTIF(A1:C1;"no")=3;"no";IF(COUNTIF(A1:C1;"yes")+COUNTIF(A1:C1;"N/A")=3;"yes";"-"))``

#### Gerald Higgins

##### Well-known Member
Or perhaps
=if(countif(A1:C1,"NO")>0,"NO","YES")

#### PlusX EAI

##### New Member
That would result in "no" with only one "no" in columns ?

#### Gerald Higgins

##### Well-known Member
Yes indeed, which I think is what was requested . . .

I need the final column to display No if any of the previous 3 columns show No

#### PlusX EAI

##### New Member
You are right, seems like I was thinking to complicated

L

#### Legacy 385836

##### Guest
Or perhaps
=if(countif(A1:C1,"NO")>0,"NO","YES")

This has worked perfectly!

Thanks for for your help guys!

L

#### Legacy 385836

##### Guest
Is there a way to amend this formula so it doesn't display 'Yes' if the row is blank?

#### Gerald Higgins

##### Well-known Member
What do you mean by "if the row is blank" ?

Do you mean "if all values in the range A1:C1 are blank" ?

L

#### Legacy 385836

##### Guest
Yes, so if I drag the formula down and cells A1:C1 are blank it will still show "yes". It's just so when I enter the things I need to check, the final column stays blank until either some data is entered (Yes, No, N/A).

Replies
3
Views
210
Replies
3
Views
186
Replies
5
Views
200
Replies
3
Views
131
Replies
2
Views
162

1,190,945
Messages
5,983,797
Members
439,859
Latest member
AlunM

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back