Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    How to add multiple status criteria when counting tasks

    ✭✭✭✭
    in Formulas and Functions

    I am looking to create two summary sheet fields.

    Formula 1. Count the number of tasks that are due within the next 14 days

    • Constraints:
      • Status must be "Not Started, In Progress, OR Scheduled
      • Due Date must be within the next 14 days

    Formula 2. Count the number of tasks that are due within the next 14 days but also return values of tasks where due date is older then TODAY.

    • Constraints:
      • Status must be "Not Started, In Progress, OR Scheduled
      • Due Date must be within the next 14 days but also older then TODAY.

    This formula gets me part of #1. I am having issues adding multiple statuses and getting the correct returns. Any help would be greatly appreciated.

    =COUNTIFS(Status:Status, "In Progress", [Due Date]:[Due Date], AND(@cell >= TODAY(), @cell <= TODAY(+14)))

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭✭
      Answer ✓

      Hi @Devin C. - Much like the AND function, you can use OR to add in the multiple statuses.

      Formula 1: =COUNTIFS(Status:Status, OR(@cell="In Progress", @cell="Not Started", @cell="Scheduled"), [Due Date]:[Due Date], AND(@cell >= TODAY(), @cell <= TODAY(+14)))

      Formula 2 (removing the constraint of only being in the next 14 days and not also in the past: =COUNTIFS(Status:Status, OR(@cell="In Progress", @cell="Not Started", @cell="Scheduled"), [Due Date]:[Due Date], <= TODAY(+14))

      Please let me know if those work for you!

      Amber Eakin, MSLS, M.Ed.

      Adult Education Specialist | Process Improvement Enthusiast

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭
      Answer ✓

      Hi @Devin C. - Much like the AND function, you can use OR to add in the multiple statuses.

      Formula 1: =COUNTIFS(Status:Status, OR(@cell="In Progress", @cell="Not Started", @cell="Scheduled"), [Due Date]:[Due Date], AND(@cell >= TODAY(), @cell <= TODAY(+14)))

      Formula 2 (removing the constraint of only being in the next 14 days and not also in the past: =COUNTIFS(Status:Status, OR(@cell="In Progress", @cell="Not Started", @cell="Scheduled"), [Due Date]:[Due Date], <= TODAY(+14))

      Please let me know if those work for you!

      Amber Eakin, MSLS, M.Ed.

      Adult Education Specialist | Process Improvement Enthusiast

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Amber E, You are a rock star. Thank you very much for the help and quick response.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      @Devin C. , my pleasure!

      Amber Eakin, MSLS, M.Ed.

      Adult Education Specialist | Process Improvement Enthusiast

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions