conditional formattingformulasgoogle sheets
I need a whole row to be red if the date in column C is older than today()
I have tried this basic formula
=C2:C<TODAY()
but that makes every row that is still empty also red.
So how to add basically "if a cell in column C is empty, do nothing"?
I can go around it by adding another condition that says when it's empty, make it white and put it above the red condition but I want to avoid unnecessary complications.
The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!
All together, you're looking at the following formula for B3 in your example:
=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")
edit
I temporarily forgot about filter, which makes this task much easier:
=COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")
Try the function COUNTA, it counts the number of cells in a range that are not empty,
and indicate more than zero for conditional rule:
Was this what you were looking for?
Best Answer