COUNTIFS with criteria, last x days

Options

I'm trying to create a formula that will allow me to determine the status of training at anytime per the due date and by simply changing the # days ([Column6]1)until training is due. For example, if I want to know the status of courses due within the next 30 days, I would change the 13 below to 30. If I wanted to know the status of courses due within the next 5 days, I would change the 13 to 5. Any thoughts? See below for screenshots.



Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    You're on the right track. Try for Course 2 Not Attempted:

    =COUNTIFS({Course 2}, "Not Attempted", {Due date}, @cell<=TODAY($[column 6]$1)

    This will also capture any that are past due as of today. You're not using a past due column. Consider adding it.

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Jennifer Lenander
    Jennifer Lenander ✭✭✭✭✭
    Options

    Thanks @Mark Cronk. Unfortunately that left me with a #UNPARSEABLE result. Any other thoughts?

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Missing a paren. Try:

    =COUNTIFS({Course 2}, "Not Attempted", {Due date}, @cell<=TODAY($[column 6]$1))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!