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?