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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!