COUNTIFS from one date through today with other criteria

edited 07/18/22 in Formulas and Functions


Needing help creating a formula. The formula has to count all the cells in the "clearance date" column that are blank with the criteria from the "application effective date" which is all dates happening 10.1.2021 through today (which means counting on a rolling basis)

I have this formula below that isn't working:

=COUNTIFS({Clearance Date Range 2}, "", {Application Effective Date Range 1}, AND(@cell >= 2021.10.1))

Any help appreciated!



  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @JamieH

    Instead of writing out the date as a text string, you'll want to use the DATE function so the formula recognizes what numbers represent the day/month/year, etc.

    Try something like this:

    =COUNTIFS({Clearance Date Range 2}, "", {Application Effective Date Range 1}, @cell >= DATE(2021, 10, 01))

    See: DATE Function



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!