Google Sheets – Conditional Formatting with IF/AND

google sheetsgoogle-sheets-custom-function

I'm trying to highlight cells in column N when the following criteria are met:

  • K = number (don't count text or blanks)
  • N = blank
  • L = date

Column K is formatted as a number

The formula I used: =AND(K:K>0, N:N="")

Unfortunately, if anything is filed in column K (text or number) and N is blank, then N is highlighted. How do I correct the formula for K:K>0? Is there a way to make it ISNUMBER?

I tried this formula, but it doesn't highlight anything
=IF(AND(ISNUMBER(M:M), ISBLANK(N:N)))

Best Answer

This does the trick:

=IF( AND(ISNUMBER(K4), ISBLANK(N4), ISDATE(L4)), COUNTIFS(K4, ">0", L4, ">"&DATE(0,1,1)))

It makes sure these conditions are met:

  • K=number (not NA)

  • N=blank

  • L=date

Modified a formula @User0 provided. Thank you!

Related Topic