#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Countif with dates

Options
edited 12/09/19

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.

• ✭✭✭
edited 04/13/16
Options

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.

• Options

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

• ✭✭✭
Options

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.

This discussion has been closed.