## Highlight duplicates based on criteria, part 1

*Article updated on January 06, 2018*

**Conditional formatting formula:**

=IF($A2=$E$1, COUNTIFS($A$2:$A$16, $E$1, $B$2:$B$16, $B2), 0)

**How to apply the conditional formatting formula in excel 2007:**

- Select the range (A2:B16)
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formual is true" window.
- Type =IF($A2=$E$1,COUNTIFS($A$2:$A$16,$E$1,$B$2:$B$16,$B2),0)
- Click Format button
- Click "Fill" tab
- Pick a color
- Click OK!
- Click OK!

Change the category (E1) and see how different cells being highlighted.

### Download excel example file

highlight-duplicates-where-adjacent-column-meets-criteria-using-conditional-formattingv2.xlsx

(Excel 97-2003 Workbook *.xls)

*This blog article is one out of two articles on the same subject.*

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2

### Functions in this article:

**IF(**logical_test;[value_if:true];[value_if_false]**)**

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

### 2 Responses to “Highlight duplicates based on criteria, part 1”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I have a list of equipment numbers that need the duplicates in that column highlighted - comparing all values regardless of Work Type, but only highlighting Work Type = TC. What would the formula be?

Work Type Equip # Result

PM RLF00424 FALSE

PM RLF01755 TRUE

PM RLF01890 FALSE

PM RLF00537 TRUE

PM RLF01530 FALSE

TC RLF01755 TRUE

TC RLF00579 FALSE

TC RLF00876 FALSE

TC RLF00537 TRUE

TC RLF00520 FALSE

Linda,

See this blog post: https://www.get-digital-help.com/2009/09/09/highlight-duplicates-where-adjacent-cell-value-meets-criteria-using-conditional-formatting-in-excel-part-2/