Flagging empty cells if date in another cell has passed

Question

Good afternoon!

I've created a test sheet to help visualize this - https://docs.google.com/spreadsheets/d/1-qHH4GfeIZz-7OBHFpp4trrFNg7l-gBHDT2MHg9tFmE/edit?usp=sharing

I'm aiming to have cells highlight only if all the following conditions are met -

• The cell in Column C is empty
• The date in Column B is not empty
• The date in Column B has passed

I've thrown together a few things that partially work but will either highlight cells in Column C even if Column B's date is blank or will highlight all cells in Column C that are past Column B's date - even if the cell in Column C is populated.

Ideally, in the example sheet, cells C2 and C3 would be highlighted while C5-C7 remain blank.

This seems incredibly simple but I'm at the end of my rope with it... Any pointers?

Answer 1

=COUNTIFS(C2,"=",B2,"<>",B2,"<"&TODAY())