Conditional formatting with AND operator

[JoepDemey]

Hello,

I have a simple request, and I thought I had the answer myself, but I must be doing something wrong....

I am using Excel 2007 and I have a sheet with 3 columns:
- Column A contains either "ja" or "nee" (yes or no in dutch)
- Column B contains numerical values
- Column C should have a conditional format of a certain color fill, when:
A is "ja" AND B > 100

I have put a conditional format on column A with the formula

Formula: =A1="ja"

I have put a conditional format on column B with the formula

Formula: =B1>100

But I can't get my conditional format on column C with this formula

Formula: =AND(A1="ja",B1>100)

I get this error:

The formula you typed contains an error.

• For information about fixing common formula problems, click Help.
• To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library group).
• If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (').

 

[Andrew Poulsom]

Do you have Dutch Excel? Maybe you need to use the Dutch equivalent of AND (EN).

 

[JoepDemey]

Do you have Dutch Excel? Maybe you need to use the Dutch equivalent of AND (EN).

No, my Excel is in English... (I tried it anyway, but it doesn't work either)

 

[StillLearningExcel]

Hello,

I have a simple request, and I thought I had the answer myself, but I must be doing something wrong....

I am using Excel 2007 and I have a sheet with 3 columns:
- Column A contains either "ja" or "nee" (yes or no in dutch)
- Column B contains numerical values
- Column C should have a conditional format of a certain color fill, when:
A is "ja" AND B > 100

I have put a conditional format on column A with the formula
I have put a conditional format on column B with the formula
But I can't get my conditional format on column C with this formula
I get this error:

When you get the error message, does it add an extra " either side of ja like so ""ja"". if it does just delete the extra " so it goes back to "ja" and it should start working

 

[JoepDemey]

When you get the error message, does it add an extra " either side of ja like so ""ja"". if it does just delete the extra " so it goes back to "ja" and it should start working

I can't check that, as the formula I enter is not even accepted. But the problem you are talking about sounds familiar too, but I don't think it has anything to do with this situation. The problem remains when I enter a simple formula such as

Formula: =AND(A1>1, B1>1)

So I think there's just a problem with the AND( , ) syntax, I just don't know why...

 

[RoryA]

My guess would be your regional settings - try:
=AND(A1>1;B1>1)

 

[JoepDemey]

My guess would be your regional settings - try:
=AND(A1>1;B1>1)

I have tried that same guess before, no luck...

by the way, it's amazing how quickly so many people are responding here, great forum!

 

[RoryA]

Can you enter that formula into a cell?

 

[JoepDemey]

I have tried that same guess before, no luck...

by the way, it's amazing how quickly so many people are responding here, great forum!

Guess what... it works! I don't understand, as I'm sure I hade tried ";" before... I must say I also added a space before the ";", but that disappeared after the formula was accepted.

But anyway, it works now, thank you all for your help!

 

[jalanen]

Thank you! ";" worked! And I tried to solve this problem already a long time. Thanks a lot.