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

Options
MC100
MC100 ✭✭
edited 12/15/23 in Formulas and Functions

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))

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @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))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!