I'm having a problem with this function. It never gets to the else part of the function.
=IF(OR(ISBLANK([Action Status]1), [Action Status]1 = "In Progress"), IF(COUNTIF([Action Status]78:[Action Status]82, ISBLANK(@cell)) = 5, "Not Started", IF(COUNTIF([Action Status]78:[Action Status]82, "Completed") + COUNTIF([Action Status]78:[Action Status]82, "Not Applicable") = 5, "Completed", IF(COUNTIF([Action Status]78:[Action Status]82, "Completed") + COUNTIF([Action Status]78:[Action Status]82, "Not Applicable") < 5, "In Progress", [Action Status]1))))
The above function is in the highlighted cell. I'm trying to auto calculate our department's overall status based on the overall status of the sheet (set in [Action Status]1). [Action Status]1 can be in In Progress, Pending, VOIDED or blank status. If it's In Progress or blank, the function calculates the department's status correctly, but if [Action Status]1 is VOIDED or Pending, it doesn't calculate and leaves the cell blank (should be whatever [Action Status]1 is set to. It never executes the else part of the function.
Thank you!
Best Answer
-
Lets try shifting your closing parenthesis...
=IF(OR(ISBLANK([Action Status]1), [Action Status]1 = "In Progress"), IF(COUNTIF([Action Status]78:[Action Status]82, ISBLANK(@cell)) = 5, "Not Started", IF(COUNTIF([Action Status]78:[Action Status]82, "Completed") + COUNTIF([Action Status]78:[Action Status]82, "Not Applicable") = 5, "Completed", IF(COUNTIF([Action Status]78:[Action Status]82, "Completed") + COUNTIF([Action Status]78:[Action Status]82, "Not Applicable") < 5, "In Progress"))), [Action Status]1)
Answers
-
Lets try shifting your closing parenthesis...
=IF(OR(ISBLANK([Action Status]1), [Action Status]1 = "In Progress"), IF(COUNTIF([Action Status]78:[Action Status]82, ISBLANK(@cell)) = 5, "Not Started", IF(COUNTIF([Action Status]78:[Action Status]82, "Completed") + COUNTIF([Action Status]78:[Action Status]82, "Not Applicable") = 5, "Completed", IF(COUNTIF([Action Status]78:[Action Status]82, "Completed") + COUNTIF([Action Status]78:[Action Status]82, "Not Applicable") < 5, "In Progress"))), [Action Status]1)
-
That worked! Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!