COUNTIF(CHILDREN() with AND for multiple condition check
Hi Folks
I'm brain fogged as I've been putting together a nest of IFs to perform status roll up values to a parent off the values of the children and am hoping someone has a thought.
I'm spinning on one bit that checks if the count of Late status is less then 0 AND count of Delayed is greater than 0. If true, then it should be Delayed. If it's False then it'll drop top the next line that checks count of Late is greater than 0
It is a long formula that is working, here is a snip of the section, in bold is the issue;
...
IF(COUNTIF(CHILDREN(), "Canceled")+COUNTIF(CHILDREN(), "Not Applicable")+COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started",
IF(COUNTIF(CHILDREN(), "Canceled")+COUNTIF(CHILDREN(), "Complete")+COUNTIF(CHILDREN(), "Not Applicable")+COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "In Progress",
IF(AND(COUNTIF(CHILDREN(), "Late")<0,(COUNTIF(CHILDREN(), "Delayed")>0,"Delayed",
IF(COUNTIF(CHILDREN(), "Late")>0,"Late",
IF(COUNTIF(CHILDREN(), "In Progress")>0,"In Progress","---")))))))))))))))))))))))
Thank you,
Shawn
Best Answer
-
Why not adjust the order? If you put the count of Late being greater than zero first, then by default it must be less than or equal to zero for anything after.
.................IF(COUNTIFS(CHILDREN(), "Late") > 0, "Late", IF(COUNTIFS(CHILDREN(), "Delayed") > 0, "Delayed", ..................
If you can prioritize the different statuses then you should be able to use this logic for the majority of the outputs. For example... If there is even one "Late" and you want that to be the absolute priority regardless of the other children, then putting that first means you can leave it out of everything after it.
Answers
-
Why not adjust the order? If you put the count of Late being greater than zero first, then by default it must be less than or equal to zero for anything after.
.................IF(COUNTIFS(CHILDREN(), "Late") > 0, "Late", IF(COUNTIFS(CHILDREN(), "Delayed") > 0, "Delayed", ..................
If you can prioritize the different statuses then you should be able to use this logic for the majority of the outputs. For example... If there is even one "Late" and you want that to be the absolute priority regardless of the other children, then putting that first means you can leave it out of everything after it.
-
Hi Paul, of course. Good grief, thank you for the fresh set of eyes and suggestion. Exactly what I needed.
Shawn
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!