CountIF Formula for Dates in the Past

I am trying to amend a current formula I am using to track enrollment. Currently, I have this formula:

=COUNTIF([Date of Injection]1:[Date of Injection]100, ISDATE(@cell)) which counts the amount of cells that have a date so my team can track how many animals we have enrolled in a certain project.

I want to change this to count the number of cells with a date but only if that date is in the past so my sheet summary data accurately reflects current enrollment.

Is this possible?


Best Answer

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓

    Hi @MirandaLang

    I think this formula should work:

    =COUNTIF([Date of Injection]1:[Date of Injection]100,<TODAY())

    I would probably adjust the range so it reads like this though: =COUNTIF([Date of Injection]:[Date of Injection],<TODAY()). That way it picks up the whole column and will be maintenance free if you have over 100 animals.

    I hope that helps.


    Matt Johnson

    Sevan Technology

    Smartsheet PLATINUM Partner


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!