2

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?

0

2 Answers 2

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:

update 2:

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

12
  • 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? 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, "") 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))). 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))) 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))) Aug 13, 2018 at 1:03
0

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
3
  • thanks, @ruben! sheets automatically converts J:J into J1:J1033 after i save the formula so there's no need to use J1 Aug 8, 2018 at 3:37
  • @quesyrahsarah Are you saying that using J:J the conditional formatting works? 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/… Aug 11, 2018 at 2:33

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .