# What formula do I use to roll up statuses?

Options
✭✭✭✭

Hi everyone, I need help!

I have these 3 statuses, Not Started, In Progress, Complete

I am trying to create a formula which rolls up the statuses for example,

if all tasks are not started, parent status should be not started,

if atleast 1 task is in progress, parent should be in progress ,

and if all tasks are complete, then parent should be complete.

Thx

• ✭✭✭✭✭✭
Options

@jerry123 I missed a closing parenthesis. Here you go:

=IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

• ✭✭✭✭✭✭
Options

There are a number of examples of this throughout the Community. Below is a thread that should help you with the logic. It uses RYG, but can be applied to your case as well.

• ✭✭✭✭
Options

Thank you!

I used this formula =IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIFS(CHILDREN(), "Not Started") > 0, "Not Started", "Complete")) works for everything else but the below logic. If more than 1 task is Not Started and some are complete it should show In Progress.

Any ideas how to fix this formula?

• ✭✭✭✭✭✭
edited 08/30/23
Options

Use one of the options that requires all the child statuses to be that option, then an IF OR statement combination, such as this:

=IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(OR(COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") < COUNT(CHILDREN())), "In Progress", "Complete"))

If all child statuses are "Not Started", then the parent status is "Not Started"

If any child statuses are "In Progress" or some (but not all) are "Complete", then "In Progress"

If neither of the above is met, then all should be "Complete" and thus the parent status will be as well.

• ✭✭✭✭✭✭
Options

@jerry123 Give this a try:

=IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> ""), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

• ✭✭✭✭
Options

@Paul Newcome hmm... it's saying incorrect argument

• ✭✭✭✭✭✭
Options

@jerry123 I missed a closing parenthesis. Here you go:

=IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")), "Complete", IF(COUNTIFS(CHILDREN(), @cell = "Not Started") = COUNT(CHILDREN()), "Not Started", "In Progress"))

• ✭✭✭✭
Options

@Paul Newcome Awesome!! That worked perfectly. THANK YOU

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!