Solved

# 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'.

Posted on 2016-09-23
25 Views
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=*
0
Question by:Dave
• 3
• 3

LVL 18

Assisted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 250 total points (awarded by participants)
ID: 41813235
formula in A1:
``````=IF(AND(COUNTIF(B1:B5,"yes")>=1,COUNTIF(B1:B5,"no")>=1),"*","")
``````
0

LVL 28

Accepted Solution

Subodh Tiwari (Neeraj) earned 250 total points (awarded by participants)
ID: 41813386
@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),"*","")
``````

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

LVL 18

Expert Comment

ID: 41813392
namaste, Subodh - thank you
0

LVL 28

Expert Comment

ID: 41813397
Namaste Crystal! :)
You're welcome.
0

Author Comment

ID: 41837317
My apologies for the delay and thank you both for your quick responses
The suggested points distribution is certainly fine with me.
Dave
0

LVL 18

Expert Comment

ID: 41837384
you're welcome, Dave ~ happy to help
0

LVL 28

Expert Comment

ID: 41839776
The chosen answers resolved the question.
0

## Featured Post

### Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.