Smartsheet formula to only apply to Parent Tasks with specific criteria
Hello,
I have been using Smartsheet for a few months but am definitely still considered a beginner. So far, I've gotten by fairly well thanks to this forum :)
I am using the following formula which calculates the % of child tasks which are Complete, and it only applies to Parent tasks. This is great, but I only want it to show on Parent tasks which are NOT Cancelled or Completed. The logic here is that I may mark an entire Parent task as Complete without worrying about all child task statuses.
Can you help me add the criteria required to get this done? Here is what I have which works:
=IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled"))))))
Thanks!
Best Answer
-
Hi @alagator28
You can add an IF statement at the very beginning of the formula that checks the Status of the current Parent row before going into your percent formula.
Try something like this:
=IF(OR(Status@row = "Cancelled", Status@row = "Complete"), "", IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled")))))))
I've made it return "blank", but you could adjust this to 100% if you'd like:
=IF(OR(Status@row = "Cancelled", Status@row = "Complete"), 1, IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled")))))))
Let me know if that helps!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @alagator28
You can add an IF statement at the very beginning of the formula that checks the Status of the current Parent row before going into your percent formula.
Try something like this:
=IF(OR(Status@row = "Cancelled", Status@row = "Complete"), "", IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled")))))))
I've made it return "blank", but you could adjust this to 100% if you'd like:
=IF(OR(Status@row = "Cancelled", Status@row = "Complete"), 1, IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Status@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled")))))))
Let me know if that helps!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you, @Genevieve P. ! Apologies for the delayed response.
I was able to get it to work, thanks to your answer. I made some slight modifications, but I'm happy with how it works now. I'm basically only showing the % of sub-tasks complete if the status is In Progress.
Here is what I ended up with.
=IF((Status@row = "In Progress"), IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Complete"))) / ((IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, COUNT(CHILDREN(Tasks@row)))) - (IF(COUNT(CHILDREN([Parent Tasks]@row)) > 0, (COUNTIF(CHILDREN(Status@row), Status@row = "Cancelled"))))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!