Forum Discussion
Conditional Format w/ Text Exception
Having a tough time figuring this one out.
I have a list of reports in a sheet. One column is full of report numbers. I want to conditionally format, highlight any duplicate report number, EXCEPT if the cell says "DRAFT".
I've tried =countif($C:$C,"<> DRAFT") but that isn't working. Any ideas?
Thanks in advance!
You may try this...
Select the whole column C and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.
=AND(COUNTIF(C:C,C1)>1,C1<>"DRAFT")
5 Replies
You may try this...
Select the whole column C and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.
=AND(COUNTIF(C:C,C1)>1,C1<>"DRAFT")
That worked, thanks!
Just so I understand the formula a bit better, what is the purpose of the ">1" part of the formula?
You're welcome!
>1 means that if there is only one occurrence of a value, the Countif formula will return 1 and if there are more than one occurrences of the same value, the Countif formula will return a count which would be greater than 1. So a value will be considered as a duplicate value only if the Countif returns a count which is greater than 1.
If that answers your query, please don't forget to mark the post with the proposed solution as an Accepted Answer which will mark your question as Solved.
