# Google sheets function IF, AND conditional formatting

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?

• 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 2:

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

• thanks for this! i added a column so J=K, K=L, L=M and removed the "NOT" from "NOT(ISBLANK(" since i actually want it to highlight the blank cells. this is what i used: =IF( AND(ISNUMBER(K1:K), ISBLANK(M1:M)), COUNTIFS(K1:K, ">0", L1:L, ">"&DATE(0,1,1))) but it seems to disregard the condition for L (has a date). it highlights the empty column M cells when L is blank and when L has a date. how can i make sure it only captures the cells that have a date in column L? – que syrah sarah Aug 8 '18 at 3:36
• i also tried this formula, but nothing highlighted: =countifs(K:K, "isnumber", L:L, ">"&DATE(1990,1,1), M:M, "") – que syrah sarah Aug 8 '18 at 3:51
• i tried adding ISDATE(L1:L) but nothing highlights. =IF( AND(ISNUMBER(K1:K), ISDATE(L1:L), ISBLANK(M1:M)), COUNTIFS(K1:K, ">0", L1:L, ">"&DATE(0;1;1))). – que syrah sarah Aug 8 '18 at 4:01
• i removed the countifs part, figuring that the first part of your formula covered my criteria, but nothing highlighted. the formula: =IF( AND(ISNUMBER(K1:K), ISDATE(L1:L), ISBLANK(M1:M))) – que syrah sarah Aug 8 '18 at 4:03
• YOU ARE AN ANGEL! thank you very much for being so patient and helping me. sorry, i forgot to tell you i added headers. your new formula works perfectly =IF( AND(ISNUMBER(K6), ISBLANK(M6), ISDATE(L6)), COUNTIFS(K6, ">0", L6, ">"&DATE(0,1,1))) – que syrah sarah Aug 13 '18 at 1:03

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

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
• thanks, @ruben! sheets automatically converts J:J into J1:J1033 after i save the formula so there's no need to use J1 – que syrah sarah Aug 8 '18 at 3:37
• @quesyrahsarah Are you saying that using J:J the conditional formatting works? – Rubén Aug 8 '18 at 4:09
• no the conditional formatting doesn't work. i was just clarifying that J1:J didn't need to be specified since gsheets automatically corrects my J:J into J1:J1033. i create a test sheet. i want blank M cells to highlight if K has a number AND L has a date. if K doesnt have a number and/or L doesnt have a date, then M cells shouldnt be highlighted even if they're blank docs.google.com/spreadsheets/d/… – que syrah sarah Aug 11 '18 at 2:33