IF Condition

[riyazpattan619]

Hi Friends,

Could you look into my doubt, and seeking a solution.

Doubt: Based on Yes/No selection the output need to change according to below conditions.




Could anyone contribute your valuable suggestion on this

Thanks
Riyaz

 

[Nebu]

May be this?

=IF(AND(COUNTIF(B1:B5,"Yes")=2,SUMIF(B1:B5,"Yes",A1:A5)=3),"Good",IF(AND(COUNTIF(B1:B5,"No")=2,SUMIF(B1:B5,"No",A1:A5)=3),"OK",IF(AND(COUNTIF(B1:B5,"No")=2,SUMIF(B1:B5,"No",A1:A5)=4),"Not Good",IF(AND(COUNTIF(B1:B5,"Yes")=2,SUMIF(B1:B5,"Yes",A1:A5)=6),"Fine",IF(AND(COUNTIF(B1:B5,"No")=2,SUMIF(B1:B5,"No",A1:A5)=8),"Average")))))

Thanks

 

[bosco_yip]

Try,

In D2 :

=IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTEC2&C3&C4&C5&C6,"Yes",1),"No",0),{"11000","Good";"00111","OK";"01011","Not Good";"10001","Fine";"11010","Average"},2,0),"")

Regards
Bosco

 

[Hui]

Riyaz

Firstly, Welcome to the Chandoo.org Forums

If you can use True/False instead of Yes/No...
and the True/False is in C2:C6

This will work

=CHOOSE(INT(SEARCH(BIN2DEC(TEXT(SUM(((C$2:C$6)*10^(ROW(C$2:C$6)-2))),"00000")),"3,11,17,26,28")/3)+1,"Good","Ok","Not Good","Fine","Average") Press Ctrl+Shift+Enter

 

[Hui]

or to handle Yes/No answers

=CHOOSE(INT(SEARCH(BIN2DEC(TEXT(SUM(((--((C$2:C$6)="Yes"))*10^(ROW(C$2:C$6)-2))),"00000")),"3,11,17,26,28")/3)+1,"Good","Ok","Not Good","Fine","Average") Press Ctrl+Shift+Enter

 

[GraH - Guido]

View attachment 49152

Try,

In D2 :

=IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTEC2&C3&C4&C5&C6,"Yes",1),"No",0),{"11000","Good";"00111","OK";"01011","Not Good";"10001","Fine";"11010","Average"},2,0),"")
Regards
Bosco

Wow, binary logic... Man, you are good, Bosco. Learning a new and creative way of looking at formulas from you. I would have made something in Nebu's style.

 

[riyazpattan619]

Dear All,

My Sincere thanks to Mr. Hui, Mr. Bosco, Mr. Nebu for providing the solution in different way. Perhaps I came up with the below one. Assuming the "Inputs" as in 'C' column.

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

Once again thank you all.

Riyaz