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)


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


  • M:M is blank

The formulas I found were:


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


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?


2 Answers 2

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

         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:

update 2:

         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? Commented Aug 8, 2018 at 3:36
  • i also tried this formula, but nothing highlighted: =countifs(K:K, "isnumber", L:L, ">"&DATE(1990,1,1), M:M, "") Commented Aug 8, 2018 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))). Commented Aug 8, 2018 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))) Commented Aug 8, 2018 at 4:03
  • 1
    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))) Commented Aug 13, 2018 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,

instead of




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 Commented Aug 8, 2018 at 3:37
  • @quesyrahsarah Are you saying that using J:J the conditional formatting works? Commented Aug 8, 2018 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/… Commented Aug 11, 2018 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.