Using IF AND and COUNTIF in one formula

[Dmerritt16]

I have 6 dropdowns with the same 30+ options to choose from in each. 10 of those options have similar attributes and I want to advise the user if they pick 4 out of 6 of these when filling in the 6 dropdowns. I have a cell off to the side of these options that will display this message. I got some help from a friend and came up with the formula below:

=IF(AND(COUNTIF(B11:D13,"Option 1")=1,COUNTIF(B11:D13,"Option 2")=1,COUNTIF(B11:D13,"Option 3")=1,COUNTIF(B11:D13,"Option 4")=1),"Four or more selections share similar attributes","")

This works if those four options are filled into any of the blanks, but I needed the rest of the similar options to be added to the formula. I thought I could continually add =1,COUNTIF(B11:D13,"Option _")
but the formula stops working if I add more. Excel doesn't tell me anything is wrong, it just doesn't show me the message anymore.

Did I accidently set this formula up to only accept four answers? I can't think of what to change to make it work no matter which of the 10 options are placed in one of the 6 drop downs.

Thoughts?

 

[jasonb75]

Because you're using AND() all of the criteria must be met, so if you set the formula for 5 options, but only selected 4 then it would not show anything.

Try

=IF(SUM(COUNTIF(B11:D13,{"Option 1","Option 2","Option 3","Option 4","Option 5"}))<3,"","Four or more selections share similar attributes")

edit:- copy the formula from here, not the email notification. I noticed a missing ) after submitting the reply.

 

[Dmerritt16]

You're a wizard.

This was perfect. I did change the 3 to a 4 so it would have to be 4 or more, but WHO CARES because this was one of my favorite online experiences ever.

Thank you, thank you, thank you.???

 

[jasonb75]

You're welcome!

Thanks for the feedback