Using AND with multiple conditions for formatting google sheets/excel

Question

I'm trying to highlight a cell if 3 different conditions are met in 3 other different cells.

A       B        C        D

Highlight A: if B is greater than 5, C is empty, and D doesn't have a duplicate

When I use these formulas individually, they work:

=AND(b:b,>5)

=AND(isblank(c:c))

=AND(countif(A:A,A1)<2)

But when I combine them this way, they don't work:

=AND(b:b,>5),(isblank(c:c)),(countif(A:A,A1)<2)

What am I doing wrong?

Also, if a value occurs multiple times, is there a formula to highlight all but the first occurrence?

Answer 1

Highlight A: if B is greater than 5, C is empty, and D doesn't have a duplicate

apply to range A1:A:

=(B1>5)*(C1="")*(COUNTIF(D:D, D1)<2)

---

Also, if a value occurs multiple times, is there a formula to highlight all but the first occurrence?

use shorter:

=COUNTIF(A$1:A1, A1)>1

or:

=ARRAYFORMULA(COUNTIFS(A$1:A, A1, ROW(A$1:A), "<="&ROW(A1))>1)

Answer 2

The AND function requires additional brackets:

=AND((b:b>5),(isblank(c:c)),(countif(A:A,A1)<2))