COUNTIFS date range less than 90 days from date in cell

Hey all, I can't find anything on this and struggling to come up with a formula looking at last 90 days from a set date. Please see attached screenshot for my current formula but I feel it shouldn't be this difficult and is probably something easy but currently stuck. My formula is not working because I have dates in the future which doesn't make sense. Thank you!



  • Khasim
    Khasim ✭✭✭✭✭

    Hi @Justin Ramos Could you please elaborate on your question?

    Thanks & Regards


    SSPM Consultants

    Email ID: [email protected]

    Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

  • This is my current formula,

    =COUNTIFS({COMPLETED-Service Original install date}, AND(@cell >= weeks@row - 90, @cell <= weeks@row))

    I am using a metrics roll up sheet to look at our service calendar. I need to know a count of services in the last 90 days from the original install date off the date@row (every Monday starting the week) and then I can know by week if we are seeing any trends.

    As a company, we look at # of services created from an original install date in the last 90 days as the benchmark to know how efficient our installations are and I need to know what it was 90 days from the date at the cell.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Justin Ramos

    To exclude the future dates add another term to your AND()

    =COUNTIFS({COMPLETED-Service Original install date}, AND(@cell >= weeks@row - 90, @cell <= weeks@row, @cell<=TODAY()))

    Will this work for you?


  • I think I figured out why there are dates in the future, because the weeks@row-90. To get an accurate depiction of what I'm looking for, I just have to make a report showing only dates from today and the past since the moving 90 day window hasn't reached those future dates yet, if that makes sense. Thank you!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Did the adjustment that I added to the formula solve the future dates, or are you still having the same problem?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!