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?

up vote 1 down vote accepted
  • 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:

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 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 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 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 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))) – que syrah sarah Aug 13 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

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 at 3:37
  • @quesyrahsarah Are you saying that using J:J the conditional formatting works? – Rubén Aug 8 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 at 2:33

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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