I would like to populate one cell (A1) with '*' if a range of 5 other cells (B1:B5) contain at least one instance of 'yes' and at least one instance of 'no'.

Hello
I would like to populate one cell (A1) with '*' if a range of 5 other cells (B1:B5) contain at least one instance of 'yes' and at least one instance of 'no'.  As an example, if B1 = yes, B2 =yes, B3=no, B4=yes, B5=yes then A1=*
Thanks for your help
DaveAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Crystal
I think the condition >0 is more correct as per the logic i.e. to check if any instance occurs like this. :)

=IF(AND(COUNTIF(B1:B5,"yes")>0,COUNTIF(B1:B5,"no")>0),"*","")

Open in new window


And this can be also written as
=IF(AND(COUNTIF(B1:B5,"yes"),COUNTIF(B1:B5,"no")),"*","")

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
formula in A1:
=IF(AND(COUNTIF(B1:B5,"yes")>=1,COUNTIF(B1:B5,"no")>=1),"*","")

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
namaste, Subodh - thank you
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Namaste Crystal! :)
You're welcome.
0
 
DaveAuthor Commented:
My apologies for the delay and thank you both for your quick responses
The suggested points distribution is certainly fine with me.
Dave
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Dave ~ happy to help
धन्यवाद, Subodh (not sure of your dialect -- Google helped me)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The chosen answers resolved the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.