What formula do I use to roll up statuses?

jerry123

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

Paul Newcome

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

Paul Newcome

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.

https://community.smartsheet.com/discussion/108803/how-to-have-child-rows-calculate-health-and-then-roll-that-up-to-parent-rows-with-different-criteria

jerry123

Hi @Paul Newcome,

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?

Appreciate your help!!

Nick Korna

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.

Paul Newcome

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

jerry123

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

Paul Newcome

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

jerry123

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

Paul Newcome

Happy to help. 👍️