Help with Roll Up formula
Please help! I have a formula in my status column that works with the exception of one scenario.
If I have say 5 children and of those, 4 with a status left blank and 1 that is "Complete", it is rolling up to "In Progress" as I need it to. When it changes to more than 1 "Complete", it rolls up to "Complete." If there are any number of "Complete" status children but even just 1 blank status, I need it to Roll up to "In Progress."
I have tried switching this around a million different ways and just can't figure it out.
Here is my formula:
=IF(COUNTIF(CHILDREN(), " ") + COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "Not Started",
IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "") > 0 = COUNT(CHILDREN()), "In Progress",
IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "Complete",
IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress",
IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete",
IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A",
IF(COUNTIF(CHILDREN(), "Off Track") > 0, "Off Track",
IF(COUNTIF(CHILDREN(), "At Risk") > 0, "At Risk",
IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress",
IF(COUNTIF(CHILDREN(), "On Hold") > 0, "On Hold", "In Progress"))))))))))
With 1 Complete:
With more than 1 Complete:
I appreciate any help you can offer!
Answers
-
Hey!
This MAY be an easy fix -
One little quirk you'll run into is in referencing children of a column where some items are blank - it doesn't count it as a child if there's nothing there - despite the row being indented.
I would work around this by referencing your primary column as the baseline comparison to your Countifs -
First portion of your formula:
=IF(COUNTIF(CHILDREN(), " ") + COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN([Primary Column])), "Not Started",
IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "") > 0 = COUNT(CHILDREN([Primary Column])), "In Progress",
IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN([Primary Column])), "Complete",
Substituting [Primary Column] with the name of your primary column - might be [Sales Activities]
Let me know if that fixes your issue or not - I'd love to try to help more if it doesn't!
-Jon Mark
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!