debugging for formula with many IFs and ORs...

Hi,

I have following conditions for a formula:

This survey analyizes 2 questions and deduces a recognition score which is calculated as following
If E8 is "-" or "do not remember", do nothing

else, if E6 is either GOLD or PLTN
then count range E8:E9 for "YES" and if counted amount is more than "1", return value "2"
if counted amount is less than "1", return "0"

Else, if E6 is "CLUB"
then check if E8 is equal to "Yes" then return 1
if cell E8 equal to "No" return return "0"

My formula is this:

=IF(OR(E8="-",E8="Do not remember"),"",IF(OR(E6="PLTN",E6="GOLD"),IF(COUNTIF(E8:E9,"Yes")>=1,"2",),IF(C6="CLUB",COUNTIF(E8,"Yes"),0)))

now the problem is that if the cell value in E6 is "CLUB" and "E8" is "Yes", the formula return "False" which actually should be returned as "1".

I believe that I am missing an IF definition somewhere but just cant figure it out... any help would be much appreciated.

Thanks,
A2k
Recognition2.xlsx

Hey guys, thanks for the help.

I tried all formulas and so far and I can only confirm that Benishiryo's edited formula and Fotis1991 contribution are doing the trick

=IF(OR(E8="-",E8="Do not remember"),"",IF(AND(OR(E6="PLTN",E6="GOLD"),COUNTIF(E8:E9,"Yes")),2,IF(AND(E6="CLUB",E8="Yes"),1,0)))

OR

=IF(AND(E6="Club",E8="yes"),1,IF(E8="No",0,IF(OR(E8="-",E8="Do not remember"),"",IF(OR(E6="PLTN",E6="GOLD"),IF(COUNTIF(E8:E9,"Yes")>=1,2),IF(C6="CLUB",COUNTIF(E8,"Yes"),0)))))

as for icestationzbra, I do like your short and compact formula alot, but it doesnt work if E6 is PLTN and the counted YES in E8:E9 are only 1. I adjusted corrected the formula to

=IF(OR(E8="Yes",E8="No"),IF(E6<>"Club",IF(COUNTIF(E8:E9,"Yes")>=1,2,0),IF(E8="Yes",1,0)),"")

and it works now.

Thanks alot for all the suggestions guys, much appreciate it!
A2k