Countif with dates

Derek Gyllenhammer

I have a column of dates. I want to have a single cell on a different column that tells me how many of those dates are in the past. 
I have tried to use Countif(column9;column109, "<=Today(0)") with no luck.

All help appreciated.

Rob Hagan

Hi Derek,

You could introduce a column alongside that column of dates that did the calculation IF(cellreference<TODAY(),1,0) on every row and then summed that new column.

Cheers,

Rob.

Derek Gyllenhammer

When I try this, I get "Missing or invalid parameters"

 

Rob Hagan

Hi Derek,

Let's say you start with a blank sheet and you have a 100 dates in Column2 starting from row 1. In the first row of Column3 you put the formula =IF([Column2]1 < TODAY(), 1, 0); in the second row of Column3 you put the formula =IF([Column2]2 < TODAY(), 1, 0); and so on. In row 1 of Column4 you put the formula =SUM([Column3]1:[Column2]100). The value shown in row 1 of Column4 will be the number of dates (of the 100 dates) that are earlier than today.

Does that help?

Cheers,

Rob.