CODEWITHSUNDEEP
excelconditional-formatting
ExReey
ExReey

Reputation: 13

Excel - conditional formatting with multiple criteria doesn't work

I'm trying to color a cell red when the contents matches (at least) one of two other cells.

I've been trying this:

=AND(COUNTIF(CB17:CE17;CB55)=1,COUNTIF(CB18:CE18;CB55)=1)

and this:

=AND(COUNTIF(CB17:CE17;CB55)=1,COUNTIF(CB18:CE18;CB55)=1) = TRUE

but Excel doesn't recognize this as a formula ("There's a problem with this formula. Not trying to type a formula? ...")!

I can't get any conditional formatting formula to work with the AND operator it seems (and my Excel is the English version).

Any advice?

Edit:
some screenshots to clarify:

Error I get when using AND or the '+' operator in the formula: Error I get when using AND or the '+' operator in the formula

Current contents of the criteria cells: Current contents of the criteria cells

Upvotes: 0

Views: 691

Answers (1)

LPChip
LPChip

Reputation: 884

AND will be true of the expression inside results to true.

First of, you do not need to evaluate to 1 for AND to work. Secondly, you used ; and , instead of ;. You mentioned changing them to ; solved it.

So =AND( COUNTIF(...), COUNTIF(...) ) would already be enough.

Your formula would be =AND(COUNTIF(CB17:CE17;CB55);COUNTIF(CB18:CE18;CB55))

Now that being said, you want it to work for either of the values being true, that means you need an OR operator, not AND. The syntax is basically the same, just replace AND with OR. Your formula would be =OR(COUNTIF(CB17:CE17;CB55);COUNTIF(CB18:CE18;CB55))

Now since your ranges are CB17:CE17 and CB18:CE18 and they both evaluate to CB55, you could of course simply use =COUNTIF(CB17:CE18;CB55) which would be a lot simpler. I have explained the AND so you can learn from it, in case you really need to use more ranges and/or evaluate against different cells.

Upvotes: 1

Related Questions