CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Brandon Park
Brandon Park

Reputation: 77

Highlight All Duplicate Cells if any of cells is empty in another column

Example in this link, provided by Matt

I want to highlight all cells that have duplicates if and only if among the duplicates, there's a cell in another column that's empty. Using conditional formatting on Google Sheets.

DUPLICATE | A
DUPLICATE | C
DUPLICATE | F

These duplicate cells should not be highlighted because none of the columns next to it are blank.

DUPLICATE | B
DUPLICATE | 
DUPLICATE | G

All 3 duplicate cells should be highlighted because at least one duplicate cell as a column next to it that's blank.

Needless to say, it should not be highlighted if there are no duplicates. This is what I tried:

=IF(ISBLANK(L2:L),COUNTIF($D$2:D,D2)>1)

I also tried:

=AND(COUNTIF($D$2:D,D2)>1,ISBLANK(L2:L))

Both of these functions only highlight the cell that have duplicates and is blank on the column L, and does not highlight the remaining duplicates, presumably because their cells' L column is not blank.

Help would be appreciated.

Upvotes: 0

Views: 615

Answers (1)

basic
basic

Reputation: 11968

You can use COUNTIFS:

=COUNTIFS($A:$A,$A1,$B:$B,"")*(COUNTIF($A:$A,$A1)>1)

enter image description here

Upvotes: 1

Related Questions