COUNTIFS - using a date

Options

Hi - looking for help with what I think would be an easy formula.

What I want to do is create the number of "Complete" tasks I have that are due BEFORE 3/31/2021.

Can anyone help with my formula and point out an error? I get UNPARSEABLE as a result.

=COUNTIFS(Status2:Status571, "Complete", [Due Date2:DueDate571,<"3/31/2021")

Thanks so much!

Lynda🧐

Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“
    Options

    =COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell<DATE(2021, 3, 31)))

    Mark


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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“
    Options

    Glad you found a solution. Happy to help. You had a couple syntax errors. I added the ISDATE to avoid getting an error.

    Thanks for contributing to the Community.

    Mark


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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“
    Options

    Try this:

    =COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell>DATE(2021, 3, 31), @cell<DATE(2021, 5, 2)))

    Mark


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

  • Lynda Ross
    Lynda Ross ✭✭
    Answer βœ“
    Options

    @Mark Cronk - again, fabulous! Thank you so much! I haven't worked with many AND & ISDATE functions so I appreciate your help!

    Happy St. Patty's Day!

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“
    Options

    =COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell<DATE(2021, 3, 31)))

    Mark


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

  • Lynda Ross
    Lynda Ross ✭✭
    Options

    OMG! Fabulous!

    Yes, it sure answered my question!

    Thankd you so much! I'm an avid learner!

    Thank you!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“
    Options

    Glad you found a solution. Happy to help. You had a couple syntax errors. I added the ISDATE to avoid getting an error.

    Thanks for contributing to the Community.

    Mark


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

  • Lynda Ross
    Lynda Ross ✭✭
    Options

    @Mark Cronk ,

    So now to add to your corrected formula, what I now need is: Tasks with the "Complete" Status that fall between two dates. Working from your wonderful formula I tried this but it comes back unparseable.

    =COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell>DATE(2021, 3, 31), AND(ISDATE (@cell), @cell<DATE(2021, 2021, 5, 2))))

    Is the thinking wrong that I can have two AND functions - one with a < and another

    >FUNCTION?

    Thanks again!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer βœ“
    Options

    Try this:

    =COUNTIFS(Status2:Status571, "Complete", [Due Date]2:[Due Date]571, AND(ISDATE(@cell), @cell>DATE(2021, 3, 31), @cell<DATE(2021, 5, 2)))

    Mark


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

  • Lynda Ross
    Lynda Ross ✭✭
    Answer βœ“
    Options

    @Mark Cronk - again, fabulous! Thank you so much! I haven't worked with many AND & ISDATE functions so I appreciate your help!

    Happy St. Patty's Day!

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

    Happy to help any time.

    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!