I have a parent row that needs to populate with appropriate 'Status' based on it's children
![PeggyLang](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I have many parent rows with many children, in some cased grandchildren. Each child (or grandchild) has a status. Status choices are;
- Not Applicable
- Not Started
- In Progress
- Complete
I have to account for the following possibilities;
I have been able to write a formula for the first 4;
I am having great difficulty writing the remaining 3 (#'s 5, 6 & 7) IF statements. As well, I'm not entirely sure where they should be inserted.
Any help would be and always is GREATLY appreciated.
Best Answers
-
One thing to keep in mind with nested IF statements is that it will stop on the first true value. This means that to get to the second IF, the first must be false. To get to the third IF then the first and second must be false.
It also looks to me like you want to essentially ignore the "Not Applicable" unless all are "Not Applicable". This means you can (for example) combine 2 and 6 into a single IF to say that if the count of "Complete" and "Not Applicable" is equal to the count of children then output "Complete". You can also combine 3 and 7.
So if we leverage this logic we can actually say something more like this:
If all are "Not Applicable" then output "Not Applicable".
If all are either "Not Applicable" or "Complete" then output "Complete".
If all are either "Not Applicable or "Not Started" then output "Not Started".
Every other combination would output "In Progress".
=IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNT(CHILDREN()), "Not Applicable", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Not Started")) = COUNT(CHILDREN()), "Not Started", "In Progress")))
-
Happy to help. 👍️
Honestly... My take on formulas and solutions is driven more by experience with being lazy. Haha. I don't want to work any harder than I really need to, so I am always evaluating formulas and whatnot to try to find the most efficient method to accomplish my goals.
Answers
-
They would need to be inserted at the beginning of your formula because you are looking at multiple criteria and then you would flow to the single criteria if the multiple criteria was not met. If you keep the single at the beginning, then you will never make it to the multiple because the single options would have already been met and the formula executed from that point.
-
@Heather_Verde your explanation makes no sense to me as there would only be one scenario for any individual situation.
-
Ok. I have just found that when I do formulas, they tend to work better when I have the multiple condition items first. Sorry if it doesn't make sense.
-
@Andrée Starå I'm wondering if you have any ideas on this question?
-
One thing to keep in mind with nested IF statements is that it will stop on the first true value. This means that to get to the second IF, the first must be false. To get to the third IF then the first and second must be false.
It also looks to me like you want to essentially ignore the "Not Applicable" unless all are "Not Applicable". This means you can (for example) combine 2 and 6 into a single IF to say that if the count of "Complete" and "Not Applicable" is equal to the count of children then output "Complete". You can also combine 3 and 7.
So if we leverage this logic we can actually say something more like this:
If all are "Not Applicable" then output "Not Applicable".
If all are either "Not Applicable" or "Complete" then output "Complete".
If all are either "Not Applicable or "Not Started" then output "Not Started".
Every other combination would output "In Progress".
=IF(COUNTIFS(CHILDREN(), @cell = "Not Applicable") = COUNT(CHILDREN()), "Not Applicable", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Complete")) = COUNT(CHILDREN()), "Complete", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Applicable", @cell = "Not Started")) = COUNT(CHILDREN()), "Not Started", "In Progress")))
-
@Paul Newcome I have been struggling with this for DAYS!!!!!
THANK YOU, THANK YOU, THANK YOU!!!!!
Can you recommend some articles, videos, teachings that will help me to see things the way you did?
-
Happy to help. 👍️
Honestly... My take on formulas and solutions is driven more by experience with being lazy. Haha. I don't want to work any harder than I really need to, so I am always evaluating formulas and whatnot to try to find the most efficient method to accomplish my goals.
-
@Paul Newcome Thank you for your contribution, I totally agree with Peggy: THANK YOU!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!