# 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

• ✭✭✭✭✭✭
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

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

• ✭✭
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"?

• ✭✭✭✭✭✭
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"))`

• ✭✭
Options

Hi @Mary_A Thank you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!