Formula for status column with 3 options: Complete, In Progress, Or Not Started
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?
Best Answer
-
Try something like this...
=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress"))
Answers
-
Try something like this...
=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress"))
-
@Paul Newcome you did it again!!! You have posted EXACTLY what I needed in the moment.
:)
-
@PeggyLang I am glad my posts have been helpful to you!
-
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?
-
@GiddyUp_Penski_File Are you able to provide a screenshot of this happening along with the exact formula you are using?
-
@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"))))
-
@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.
-
Try this:
=IF(COUNTIFS(CHILDREN(), OR(@cell = "", @cell = "Not Started")) = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "In Progress"))
-
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"))))
-
Is your latest formula actually working as expected for all variations?
-
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".
-
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"))
-
@Paul Newcome , legend! That works perfect. Thanks so much for your assistance with this one, I have learnt something new for sure.
-
Happy to help. 👍️
-
@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.
