I'm having a problem with this function. It never gets to the else part of the function.

Lisa Vercellone

=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!

Paul Newcome

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)

Paul Newcome

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)

Lisa Vercellone

That worked! Thank you!

Paul Newcome

Happy to help. 👍️