Conditional formatting formula required for day of week and a value

[laura_pink]

Hi,
I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of the week i.e. "Mon".
I need to set some conditional formatting so that when column C is "Mon" and column B is greater than 415, then the value in column B turns red.

I have tried this and its not working:
=AND($C$3:$C$2734="Mon",$B$3:$B$2734>415) I have also tried it the other way round =AND($B$3:$B$2734>415,$C$3:$C$2734="Mon")
I have also tried it without the commas around Mon - nothing works.

Any ideas? Many Thanks,
Laura

 

[Fluff]

My pleasure?

 

[laura_pink]

 

[laura_pink]

Before you go... sorry.. I thought if I wanted to add some more categories such as values between 394 and 365 - turn a different colour and so on it would be the same? However when I copied your last formula its not working. Are there only so many formats you can apply? Thanks

 

[Fluff]

In what way is it not working?
Also what is the formula you are using?

 

[Joe4]

When you have multiple rules, if there is any "overlap", you want to make sure that they are in the correct order.
You can also choose to "stop" when certain conditions are met.
See here for a complete explanation along with images: Understanding Excel's conditional formatting rules can help prevent unintended results

 

[laura_pink]

Sorry for the delay. I just tried:

=AND($C3="Mon",$B3>=394,$B3<=365)
I don't think there is any overlap?

 

[Joe4]

=AND($C3="Mon",$B3>=394,$B3<=365)

You have the signs backwards.
How can something be greater than 394 and less than 365 at the same time? It is impossible.

 

[laura_pink]

Oh dear. I’ve definitely had enough of this for one day!! Thanks for helping