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

Derek Gyllenhammer
Derek Gyllenhammer ✭✭
edited 12/09/19 in Archived 2016 Posts

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.

· Share on FacebookShare on Twitter

Comments

  • Rob Hagan
    Rob Hagan ✭✭✭✭
    edited 04/13/16

    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.

    · Share on FacebookShare on Twitter
  • Derek Gyllenhammer
    Derek Gyllenhammer ✭✭

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

    Β 

    · Share on FacebookShare on Twitter
  • Rob Hagan
    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.

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories