Forum Discussion

LEna25's avatar
LEna25
Copper Contributor
Oct 25, 2022

Conditional Formatting

 I am trying to find a way to highlight one cell if there is an X  in one of twelve cells. I have been using this rule =COUNTIFS($C$2:$E$2,"X") and it works great, but only for two cells. If I put more then two cells in the formula I get an error message. For example if I change the formula to this: =COUNTIFS($C$2:$E$2:$Q$2,"X") I get the message that I may not use reference operators. Why does it work for two cells but not more then two?  What am I missing? 

 

excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 25, 2022

    LEna25 

    =COUNTIF(B1:M1,"X")=1

    You can try this rule for conditional formatting.

    =$N$1:$N$7

    This is the range the formatting applies to in my example.

     

Resources