# Large IF statement?

#### Caleab

##### Board Regular
This shouldn't be that hard, but I can't seem to land it correctly.

I have columns A-E.
Column A is where we are writing the formula.
The formula needs to check Columns B,C,D for a "YES" or "NO" value.
I need it basically to check to see if B,C,D have a NO, if not AND D="YES", put a certain value in the A column (in this case, make it "FIND")

I'll also use this to have it check for different variations of this.

Thanks.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Barrie Davidson

##### MrExcel MVP
I'm sure someone (read: Aladin) will come up with a slicker formula solution, but here goes:

=IF(SUMPRODUCT((B1:D1="YES")+0)=0,"All NO's",IF(SUMPRODUCT((B1:D1="YES")+0)=3,"All YES's",IF(SUMPRODUCT((B1:D1="YES")+0)=1,CHOOSE(MATCH("YES",B1:D1,0),"1 YES in B","1 YES in C","1 YES in D"),CHOOSE(MATCH("NO",B1:D1,0),"1 NO in B","1 NO in C","1 NO in D"))))

Just change the results for:
• "All NO's" - change this string to what you want if they're all NO's.
• "All YES's" - change this string to what you want if they're all YES's.
• "1 YES in B" - change this to what you want if there is only one yes in column B.
• "1 YES in C" - see above.
• "1 YES in D" - see "1 YES in B".
• "1 NO in B" - change this to what you want if there is only one no in column B.
• "1 NO in C" - see above.
• "1 NO in D" - see "1 NO in B".

Ugly I know, but it's all I got.

Regards,

#### fairwinds

##### MrExcel MVP
Hi,

A different interpretation from me:

=IF(AND(B2<>"NO",C2<>"NO",D2="YES"),"FIND","")

#### Nimrod

##### MrExcel MVP
Code:
``=IF(AND(COUNTIF(B:D,"NO")=0,COUNTIF(D:D,"YES")>0),"find","")``

#### Yogi Anand

##### MrExcel MVP
Or ... for the stated condition ...

=IF(COUNTIF(B2:C2,"No")=0,IF(D2="Yes","Find"))

#### Caleab

##### Board Regular
Just wanted to update. I got taken off this project for awhile, but then placed back and my focus was drawn to this.

I really appreciate all the replies. It gave me some options to think about and Fairwinds' reply seemed the most simplistic and easiest to change if need be in the future.
So the final formula that I'm using is:
=IF(AND(G3<>"Yes",H3<>"Yes",I3<>"Yes",J3<>"Yes",K3="No"),"FIND","OK")

It's an all or nothing formula. If any G3:J3 = Yes, when K3 is No then it's a FIND, otherwise, it's OK. (If anyone's interested, it's a quality audit scoresheet).

Again, thanks all for the help!

#### Caleab

##### Board Regular
hmm One more little part i need to add to this.

I usually use little IF statements to keep my spreadsheets clean.
i.e. =IF(G3=0,"",A3/G3)

Just to keep the cell blank if there's no data to use. (instead of the #DIV/0 or another msg)

How can I apply this same type thing to this formula?
=IF(AND(G3<>"Yes",H3<>"Yes",I3<>"Yes",J3<>"Yes",K3="No"),"FIND","OK")

#### fairwinds

##### MrExcel MVP
Try:

=IF(G3&H3&I3&J3&K3="","",IF(AND(G3<>"Yes",H3<>"Yes",I3<>"Yes",J3<>"Yes",K3="No"),"FIND","OK"))

But maybe you do not need all 4 refs in the beginning. It depends on how your sheet is used.

#### Caleab

##### Board Regular
Instead of using & (and), is it possible to use OR
I'd like there to be nothing in the cell unless the referring cells have values.

#### fairwinds

##### MrExcel MVP
Sure:

=IF(COUNTA(G3:K3)=4,IF(AND(G3<>"Yes",H3<>"Yes",I3<>"Yes",J3<>"Yes",K3="No"),"FIND","OK"),"")

Replies
3
Views
143
Replies
3
Views
71
Replies
1
Views
141
Replies
2
Views
59
Replies
6
Views
345

1,195,640
Messages
6,010,868
Members
441,571
Latest member
stolenweasel

### 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