Large IF statement?

[Caleab]

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.

Let me know if you need more info.


Thanks.

 

[Barrie Davidson]

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]

Hi,

A different interpretation from me:

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

 

[Nimrod]

=IF(AND(COUNTIF(B:D,"NO")=0,COUNTIF(D:D,"YES")>0),"find","")

 

[Yogi Anand]

Or ... for the stated condition ...

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

 

[Caleab]

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]

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]

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]

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]

Sure:

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