Count and Countif Formula adding 4th Statement
I have an existing formula:
=IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") = COUNT(CHILDREN(Status@row)), "Not Started"))))
which works perfectly.
I want to add a statement to accommodate a "Canceled" project. I tried adding it below; however, it says it is not the right syntax.
=IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), “Canceled”) = COUNT(CHILDREN(Status@row)), “Canceled, IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") = COUNT(CHILDREN(Status@row)), "Not Started"))))
Does anyone have any thoughts on what might be wrong?
Best Answer
-
See if this fixes it:
=IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Canceled") = COUNT(CHILDREN(Status@row)), "Canceled", IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") = COUNT(CHILDREN(Status@row)), "Not Started")))))
I found a few "incorrect versions" of quotation marks, one missing quote, and also one missing closing parenthesis at the very end of the formula. Let me know if this does not fix it.
Answers
-
See if this fixes it:
=IF(COUNT(CHILDREN(Status@row)) = 0, " ", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") = COUNT(CHILDREN(Status@row)), "Complete", IF(COUNTIF(CHILDREN(Status@row), "Canceled") = COUNT(CHILDREN(Status@row)), "Canceled", IF(COUNTIF(CHILDREN(Status@row), "Not Started") + COUNTIF(CHILDREN(Status@row), " ") = COUNT(CHILDREN(Status@row)), "Not Started")))))
I found a few "incorrect versions" of quotation marks, one missing quote, and also one missing closing parenthesis at the very end of the formula. Let me know if this does not fix it.
-
Carson
That fixed it. I figured it was something simple.
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!