Search code examples
google-sheetsgoogle-sheets-formulaarray-formulascountifgs-conditional-formatting

Using AND with multiple conditions for formatting google sheets/excel

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?

Solution

  • 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)

    0

    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)

    0