How can I add an OR condition to an existing formula?

MC100

Created a hidden column in my project plan for overdue tasks .. box will check automatically if end date is in the past and Status=Not Started. This is working fine.

Need to add OR condition .. so if end date is in the past and Status=Not Started OR In Progress.

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), <TODAY(), DESCENDANTS(Status@row), "Not Started") > 0, 1, 0), IF(AND([End Date]@row < TODAY(), Status@row = "Not Started"), 1, 0))

Paul Newcome

@Eric Law You still ended up using the OR statement just with the wrong syntax and not accounting for different logic on parent and child rows.

@MC100 Try this:

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), <TODAY(), DESCENDANTS(Status@row), OR(@cell = "Not Started", @cell = "In Progress")) > 0, 1, 0), IF(AND([End Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress")), 1, 0))

Eric Law

https://community.smartsheet.com/discussion/114455/how-can-i-add-an-or-condition-to-an-existing-formula

Instead of adding an OR, it will be easier to just put in an IF statement for that. i.e.

=IF(Status@row = OR(@cell="Not Started", @cell="In Progress"), IF(what you currently have in the checkbox formula), 0)

MC100

This is not working for me

Paul Newcome

@Eric Law You still ended up using the OR statement just with the wrong syntax and not accounting for different logic on parent and child rows.

@MC100 Try this:

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIFS(DESCENDANTS([End Date]@row), <TODAY(), DESCENDANTS(Status@row), OR(@cell = "Not Started", @cell = "In Progress")) > 0, 1, 0), IF(AND([End Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress")), 1, 0))

MC100

Bazinga! This did the trick.. thank you so much!

Paul Newcome

Happy to help. 👍️