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.

    Using IF, OR, and CONTAINS, ISBLANK to track status of actions

    ✭✭
    in Formulas and Functions

    Hello, I'm attempting to autofill the primary row for each project with subtasks under each project. The status of subtasks vary from "Not Started", "In Progress", "Outstanding", "Complete".

    I'm trying to create a formula that examines the set of rows' status under the project to summarize the overall status of the project. I'm trying to simplify it by being either "In Progress" or "Complete"

    Is there a way to do this?


    =IF(OR(CONTAINS("In Progress", [Status of Actions]71:[Status of Actions]76), CONTAINS(ISBLANK([Status of Actions]71:[Status of Actions]76, [Status of Actions]71:[Status of Actions]76))), "In Progress", "Complete")

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      edited 07/07/22 Answer ✓

      This will allow the parent task to be "Not Started", "Complete", or "In Progress"


      =IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Not Started"), "Not Started", "In Progress"))

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭
      edited 07/06/22

      What about this?

      If the total number of subtasks (determined by counting the number of values in the "Primary Column", in this case Task) is equal to the number of rows where the status is "Complete", then the parent row is "Complete", else it is "In Progress".

      =IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", "In Progress")

      · Share on FacebookShare on Twitter
    • ✭✭

      @SJ Sellers Brilliant, this seems to work. Never thought about using the CHILDREN function. Thank you so much! For now this seems to be great, wondering if there would be a way of having another option for "Not Started"?

      · Share on FacebookShare on Twitter
    • Community Champion
      edited 07/07/22 Answer ✓

      This will allow the parent task to be "Not Started", "Complete", or "In Progress"


      =IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Complete"), "Complete", IF(COUNT(CHILDREN(Task@row)) = COUNTIF(CHILDREN(Status@row), "Not Started"), "Not Started", "In Progress"))

      · Share on FacebookShare on Twitter
    • ✭✭

      Hi @Mary_A Thank you!

      · 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