[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 47
  • Last Modified:

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
0
Dave
Asked:
Dave
  • 3
  • 3
2 Solutions
 
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
 
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:
namaste, Subodh - thank you
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now