 SOLVED

# Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES"

Trying to create an IF statement in Excel when all answers are YES, Column I turns to "YES" and if there is one answer that is "No" then column I turns to "NO". 17 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&quot

That's like

``````=IF( PRODUCT( (C4:C9="yes")*1) *
PRODUCT( (E4:E9="yes")*1) *
PRODUCT( (G4:G7="yes")*1) ,
"yes", "no")``````

If you are on 365 you don't need PRODUCT

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

Thank you Sergei...this worked fantastic.

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

@HighFiveEm , you are welcome

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

So I want to update it also if "Yes" or "N/A" then it is Yes.  But if "No" in any question then it returns a "NO".

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES"

I used the following formula which worked much better but still need to include the N/A.

=IF(COUNTIF(C4:C9,"<>YES")+COUNTIF(E4:E9,"<>YES")+COUNTIF(G4:G7,"<>YES"), "NO", "YES")

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&quot

Do you mean #N/A error returned by function or text "N/A" ?

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&quot

In general you may check the opposite - if at least one No then No else Yes. Like

``=IF(COUNTIFS(E4:E9, "No")+COUNTIFS(G4:G7, "No")+COUNTIFS(C4:C9, "No"), "No", "Yes")``

if you prefer COUNTIFS()

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

Text "N/A", if someone answers a question with a "N/A" the overall is still a "YES"

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

Thank you. Does previous formula to check "no" works, or that could be other variants except "yes", "no" and "n/a" ?

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

@Sergei Baklan

Yes, it works perfectly, thank you.

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

@HighFiveEm , you are welcome

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

If i have two columns, and i type yes or no in the boxes how can i create a formula that if both columns in a row is no the answer will be no? but if one answer is yes and 1 is no the answer should be yes?

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

Depends on how your data is structured, as variant that could be # Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&quot

A new option,  Naming your table data, one can select the columns with answers and then test for the presence of a "no"

``= AND(CHOOSECOLS(data,2,4,6)<>"no")``

There are other ways of doing this but CHOOSECOLS is one of the latest.

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

The columns are just like you are showing in the example. I cannot get any formula to work. I have tried

=IF(AND(H8:I8="No")<>"no")

=AND(CHOOSECOLS(H8:I8="no")<>"no")

I get errors with both of them.

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

1) On which Excel version/platform you are?

2) Which exactly error do you have?

At least

=IF(AND(H8:I8="No")<>"no")

never works. AND() returns  TRUE or FALSE and it never equals to "no"

# Re: Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES&

My apologies for creating confusion.  There were many aspects of traditional spreadsheets that I found distasteful, so my interest is now the new opportunities to create solutions that modern Excel offers.  However, something similar in concept is possible using INDEX (I think it was Daniel Ferry that referred to the function as 'the Imposing Index').

``= AND(INDEX(data,{1;2;3;4;5;6},{2,4,6})<>"no")``

Dressed up, even that could be written

``````= LET(
status, INDEX(data,{1;2;3;4;5;6},{2,4,6}),
passes, status<>"no",
AND(passes)
)``````

which is more open to step by step testing.