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".

excel

Formulas and Functions

SergeiBaklan

Jun 21, 2021

HighFiveEm

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

HighFiveEm

Copper Contributor

Jul 12, 2021

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")

SergeiBaklan

Diamond Contributor

Jul 12, 2021

HighFiveEm

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()

Forum Discussion

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

Resources