IF Statement for Multiple conditions

Dear Friends,

Could anyone look into my doubt and provide the solution..! Thanks

Condition 1: If 1,2 is "Yes" and the rest 3, 4, 5 are "No" then the result should be = Good

Condition 2: If 1, 2 is "No" and the rest 3, 4, 5 are "Yes" then the result should be = OK

Condition 3: If 1, 3 is "No" and the rest 2, 4, 5 are "Yes" then the result should be = Not Good

Condition 4: If 1, 5 is "Yes" and the rest 2, 3, 4 are "No" then the result should be = Fine

Condition 5: If 3, 5 is "No" and the rest 1,2, 4 are "Yes" then the result should be = Average

Can somebody help me put in putting the above in a formula in Excel sheet. I'm giving the above as example, but the scenario is same for my doubt.

Please write a Condition for the above.

Regards,

Riyaz

Assume S.No in A1 cell, Name in B1 cell, Input in C1 cell, and so on.

Excel 2016

in cell F2

=SWITCH(SUMPRODUCT(--($C$2:$C$6="Yes"),{1;2;4;8;16}),3,"Good",28,"OK",26,"Not Good",17,"Fine",11,Average,"?")

Excel 2013 or below

create a name in name manager, called TheValue

=SUMPRODUCT(--($C$2:$C$6="Yes"),{1;2;4;8;16})

in cell F2

=IF(TheValue=3,"Good",IF(TheValue=28,"OK",IF(TheValue=26,"Not Good",IF(TheValue=17,"Fine",IF(TheValue=11,"Average","?")))))

Forum Discussion