Highlight Duplicate Column ONLY When Specific Text is Present

0
votes

I only want to highlight duplicate rows ONLY if the cell in a column contains a specific value.

For example, in Column 3 if the cell contains "Apple" and Columns 1 and Columns 2 match, highlight the rows.

enter image description here

The formula for conditional formatting I'm using looks like 

=COUNTIFS($A$1:$A$1000,$A1,$B$1:$B$1000,$B1,$C$1:$C$1000,"Apple")>1

This formula highlights the whole row if ANY of the criteria is met, so it'll look like, notice Column1 on the last row:

enter image description here

I've googled so many times, but I can't seem to find an answer.

1
excelvbaduplicateshighlightworksheet-function
when I do it, it highlights the second to that last not the last, still wrong but not the reason you state. – Scott Craner

1 Answers

3
votes

use this formula, you need to include the value in column c also:

=COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,"Apple",$C:$C,$C1)>1

enter image description here