Parent Row Roll Up
I am looking to create a formula that will do the following:
If all child statuses = Complete, it will say "Complete"
If at least one child says in progress or complete, the parent row will say "In Progress"
If all child statuses say Not Started, or are Blank, it will say "Not Started"
I tried using this as a shell:
=IF(COUNTIF(CHILDREN(), "Not Started") > 0, "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Complete") > 0, "Complete", "Verify")))
But it is making the whole row say Complete is only one if complete.
Anyone have any ideas?
Comments
-
You're on the right track. Try something like this...
=IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN([Task Column]@row)), "Complete", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", ISBLANK(@cell)) = COUNT(CHILDREN([Task Column]@row)), "Not Started", IF(COUNTIFS(CHILDREN(), "In Progress") > 0, "In Progress", "Verify")))
-
I ended up going with this... definitely not the most 'right' way... but it worked.
I tried the above one and it gave me 'unparseable'
=IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") = 0), "Not Started", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") = 0), "Not Started", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") = 0), "Not Started", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") = 0), "Not Started", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") = 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") = 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") = 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") = 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") > 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") > 0, COUNTIF(CHILDREN(), "Not Started") > 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") > 0), "In Progress", IF(AND(COUNTIF(CHILDREN(), "") = 0, COUNTIF(CHILDREN(), "Not Started") = 0, COUNTIF(CHILDREN(), "In Progress") = 0, COUNTIF(CHILDREN(), "Complete") > 0), "Complete")))))))))))))))
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives