Conditional Formatting - Multiple Criteria

I have a set of data which has three columns: Persons name, profile score, engagement score.

Profile score contains a letter from a-d
Engagement score contains a number from 1-4

I am trying to create formatting that highlights the persons name when the profile score is a,b or c AND their engagement score is 1,2 or 3. So in the end only those that are a1, a2, a3, b1, b2 , b3 are highlighted.

Any suggestions?

Hi,

Welcome to the Forum.

Something like this?

Thank you - this seems like it should be the correct formula but when I input it nothing seems to be highlighted?

My information is within a pivot table if that changes anything...

The formula works if profile score is not capital letter but as they are all capital letters this seems to be the problem - how do I work around this?

maybe: =AND(OR(B2="a",B2="b",B2="c"),OR(C2=1,C2=2,C2=3)) with your range A2:An selected
lower case or upper case doesn't matter

Thank you Sandy, but as mentioned for some reason it does seem to matter. When my profile engagement scores in my data are in uppercase the formula doesn't work, but works absolutely fine when the profile engagement scores are in lower case. So my question is how can I make the formula work when the engagement scores are in upper case?

No....All m profile engagement scores are in upper case. The formula above correctly highlights the cells when the profile engagement score is in lower case (I changed some to test this as was confused as to why the formula wasnt working) but when the letter is in upper case the formula doesnt highlight anything.

See screen grab attached where the formula has been applied, the lower case letter one is highlighted only.

Why does it work when it is lower case but not upper case?data.jpg

There must be something else that is different between the cell with a and the cell with A
Because Capitalization doesn't matter to a straight A=B comparison.
="a"="A" is TRUE

Are those cells the result of a formula? What is that formula?
What does this return where B2 is a cell with A that is NOT colored.
=LEN(B2)

Can you attach a sample workbook?

attach file with your problem
picture looks great but it has little value

Sandy you can already see that is the formula I am using so it's not very helpful to repeat this.

Jonmo I agree I feel like something else may be interfering with it, I have tried to attach a dummy report but as soon as I remove all the other worksheets and important information from the document the formula works and my data becomes highlighted.

I'm sorry I know it's hard to diagnose without a worksheet but I can't attach with all my data as it is confidential, but the problem resolves itself when I delete everything.

Guess I will have to keep playing around with it....driving me crazy!

Attach sample workbook with your formula with the problem you have

original data is not necessary

What does =LEN(B2) return, when B2 is a cell containing a Capital A that is NOT triggering the conditional format ?

Agree with Sandy
Original data is not necessary.

Show formuls without data
We can plug in our own bogus data to test with.

Please attach sample book with formulas, minus sensitive data.

no file attached



Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Attached.....

C5 and C6 do NOT = "A"
Instead, they are ="A " <- notice the space at the end.

The LEN function test would have revealed this possible issue.

Ah amazing...such a simple thing. Will mark post as solved

Please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.