Google sheets function IF, AND conditional formatting

Question

EDIT 2: link to test sheet: https://docs.google.com/spreadsheets/d/1IWQWDK9piw_xFVm_48XZ8YAS_LQqXi6sd2GITLIfoAQ/edit#gid=903221057

EDIT 1: added a column, so now J=K, K=L, L=M | updated formulas to reflect the column changes

I'm trying to do conditional formatting (highlight cell) in column M (M:M) when cells in columns K and L are not blank and the corresponding cell in column M is blank. Basically, when the following criteria are met:

• K:K>0 (if cell has a number, don't count NAs)

and

• L:L has a date (so don't count blanks and NAs)

and

• M:M is blank

---

The formulas I found were:

=ARRAYFORMULA(AND(isnotblank({   

But I couldn't set it up right... not sure exactly how this gets set up.

---

=and(K:K>0, L:L>0, M:M="")

L:L>0 captures NA (seems to capture all values). How do I get it to capture only dates (not date range). I was told to use "if_date()" but this also doesn't work

---

=countifs(K:K,">0",L:L,">01/01/1998",M:M,"")

Highlights the ENTIRE column M, instead of relevant cells. When I pasted the function into a cell (instead of the conditional formatting box), it correctly counts 1 cell. However, I want conditional formatting to highlight the cell itself. The highlight range is set to M:M. Why isn't this working?

---

=countifs(K:K, ">0", L:L, "if_date()", M:M, "")

Tried this formula and nothing highlighted

---

=IF( AND(ISNUMBER(K1:K), ISBLANK(M1:M)), COUNTIFS(K1:K, ">0", L1:L, ">"&DATE(0,1,1)))

This does highlight empty cells in M when criteria are met, but also highlights the empty cells in M when criteria in L are not met (when a cell in L is blank). My criterion for L: must contain a date

---

Any idea what I'm doing wrong?

Answer 1

• in this conditional-formatting-scenario is =ArrayFormula() kind of not necessary.

• to hide #N/A you can use =IFERROR()

• to avoid #N/A you can use =ISNUMBER =ISDATE =ISBLANK =ISTEXT etc.

• and this custom formula is what you seek:

=IF( AND(ISNUMBER(J1:J);
         NOT(ISBLANK(L1:L))); COUNTIFS(J1:J; ">0"; 
                                       K1:K; ">"&DATE(0;1;1)))

it checks if J has number
and L is not blank
and then it checks if J>0
and K >1st january of year 0.

be sure you have right formatting otherwise it wont work:

update 1:

• https://docs.google.com/spreadsheets/d/

update 2:

• https://docs.google.com/spreadsheets/d/ where custom formula is:

=IF( AND(ISNUMBER(K2), 
         ISBLANK(M2), 
         ISDATE(L2)), COUNTIFS(K2, ">0", 
                               L2, ">"&DATE(0,1,1)))

Answer 2

Must of the conditional rules applied to a column should be written by using the reference of the topmost cell, in other words,

instead of

J:J

use

J1

By the other side, to test if a cell has a

• number use ISNUMBER
• date use ISDATE
• cell is not blank use ISBLANK inside NOT