Formula for status column with 3 options: Complete, In Progress, Or Not Started

Jennifer Flanagan

I have a column called "Status" with 3 options: Complete, In Progress, Or Not Started

How do i apply a formula to the parent tasks that would be not started if all the children are not started, in progress if at least one of the children is in progress or complete, and compete when all the children are complete?

Paul Newcome

Try something like this...

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress"))

Paul Newcome

Try something like this...

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress"))

PeggyLang

@Paul Newcome you did it again!!! You have posted EXACTLY what I needed in the moment.

:)

Paul Newcome

@PeggyLang I am glad my posts have been helpful to you!

GiddyUp_Penski_File

Hey there, the formula you have Provided Paul seems to work for me as well except when it comes to a correct answer for completed. All children cells are "completed" and it returns a value of "In Progress". It seems odd given the formula looks correct to me. Any ideas?

Paul Newcome

@GiddyUp_Penski_File Are you able to provide a screenshot of this happening along with the exact formula you are using?

GiddyUp_Penski_File

@Paul Newcome thanks so much for replying. I have included the screen shots. The only one not working (which I failed to previously mention) is 'if all are blank "" show "Not Started".

I have gone from your formula to this in an attempt to solve;

=IF(AND(CONTAINS("Not Started", CHILDREN()), OR(COUNTIF(CHILDREN(), "") = COUNT(CHILDREN()))), "Not Started", IF(AND(CONTAINS("In Progress", CHILDREN()), OR(CONTAINS("Completed", CHILDREN()))), "In Progress", IF(AND(COUNTIF(CHILDREN(), "In Progress") > 1, COUNTIF(CHILDREN(), "Completed") < COUNT(CHILDREN())), "In Progress", IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", "In Progress"))))

GiddyUp_Penski_File

@Paul Newcome - Cracked it!

=IF(AND(CONTAINS("", CHILDREN()), OR(CONTAINS("Not Started", CHILDREN()))), "Not Started", IF(COUNTIF(CHILDREN(),"In Progress") = 0, IF(COUNTIF(CHILDREN(), "Completed") = 0, "Not Started", "Completed"), "In Progress"))

• UPDATE - This didn't crack it. I think its missing the blanks.

Paul Newcome

Try this:

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

GiddyUp_Penski_File

Oh my gosh.... I can't believe how tough this was to crack! 🤣 I have attached pics of further problems I had with your solution Paul. Thanks so much! You were an awesome help.

=IF(AND(COUNTIFS(CHILDREN(), "", CHILDREN(), "Not Started")) = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "completed") = 1, "In Progress", IF(COUNTIFS(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(AND(COUNTIFS(CHILDREN(), "Not Started") = 1, COUNTIFS(CHILDREN(), "") <> 1), "Not Started", "In Progress"))))

Paul Newcome

Is your latest formula actually working as expected for all variations?

GiddyUp_Penski_File

NO! I thought I had checked properly. Completed is not working correctly. All others are.

Now it is working with completed on one drop down, and the remaining are blank (shows "in progress"). but anything more than 2, when the remaining are blank, gives "completed".

Paul Newcome

Try this:

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

GiddyUp_Penski_File

@Paul Newcome , legend! That works perfect. Thanks so much for your assistance with this one, I have learnt something new for sure.

Paul Newcome

Happy to help. 👍️

nnly586

@Paul Newcome Hi Paul, I have the same scenario but what is a formula I can use for "if the status is N/A" do not impact the parent row status total.

ie. if I have some child rows that are N/A, my parent status still updates to "In progress" which I don't want.