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

Options

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")

Best Answer

  • Mary_A
    Mary_A ✭✭✭✭✭✭
    edited 07/07/22 Answer ✓
    Options

    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"))

Answers

  • SJ Sellers
    SJ Sellers ✭✭✭✭
    edited 07/06/22
    Options

    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")

  • lek123
    lek123 ✭✭
    Options

    @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"?

  • Mary_A
    Mary_A ✭✭✭✭✭✭
    edited 07/07/22 Answer ✓
    Options

    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"))

  • lek123
    lek123 ✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!