IF/AND/OR Formula for Parent Row

Options

Hello! I'm looking for help to perform the following logic for Parent rows ONLY:

  1. If the status of any child row is In Progress, then set the parent row status to In Progress.
  2. If the status of ALL child rows is Complete, then set the parent row status to Complete
  3. If the status of ALL child rows is Not Started or Blank, then leave parent row status blank

This is what I started to test out two of the scenarios, but I'm receiving "UNPARSEABLE":

=IF(OR(Status2="In Progress", Status3="In Progress"), "In Progress"), IF(AND(Status2 = "Complete", Status3 = "Complete"), "Complete", "Not Started")

Tags:

Best Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    @Tony Fronza I think your brackets were messed up

    =IF(OR(Status2="In Progress", Status3="In Progress"), "In Progress", IF(AND(Status2 = "Complete", Status3 = "Complete"), "Complete", "Not Started"))

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hey @Tony Fronza

    Try adding it as the first statement in your formula:

    =IF(COUNTIF(CHILDREN(Status@row), "N/A") = COUNT(CHILDREN(Status@row)), "N/A", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") / COUNT(CHILDREN(Status@row)) = 1, "Complete", IF(COUNTIF(CHILDREN(Status@row), "Complete") > 0, "In Progress", "Not Started"))))

    Cheers,

    Genevieve

Answers

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Answer ✓
    Options

    @Tony Fronza I think your brackets were messed up

    =IF(OR(Status2="In Progress", Status3="In Progress"), "In Progress", IF(AND(Status2 = "Complete", Status3 = "Complete"), "Complete", "Not Started"))

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    Options

    @Tony Fronza

    This might be more to your liking

    =IF( COUNTIF(CHILDREN(Status@row), "In Progress")>0, "In Progress", IF( COUNTIF(CHILDREN(Status@row), "Complete") / COUNT(CHILDREN(Status@row)) = 1, "Complete", ""))

    That being said if you have all it takes is to have 1 Not Started to Blank out the STATUS.. You could have 2/3 items complete and 1/3 Not started.. would this not be "In Progress" as well?

    That might be the way you want it to Highlight those unstarted items if not this would return and "In Progress" if you have a bunch of COMPLETE and a bunch of NOT STARTED

    =IF( COUNTIF(CHILDREN(Status@row), "In Progress")>0, "In Progress", IF( COUNTIF(CHILDREN(Status@row), "Complete") / COUNT(CHILDREN(Status@row)) = 1, "Complete", IF( COUNTIF(CHILDREN(Status@row), "Complete")>0, "In Progress", "")))

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

    http://www.facilityy.com

  • Tony Fronza
    Tony Fronza ✭✭✭✭
    Options

    Thanks, @Brent Wilson! I'm receiving an error message "#DIVIDE BY ZERO" when I try to apply either formula.

  • Tony Fronza
    Tony Fronza ✭✭✭✭
    Options

    Nevermind! I was copying the second set of formulas. The first one you sent (fixed) works perfectly. Thanks again!

  • Tony Fronza
    Tony Fronza ✭✭✭✭
    Options

    @Brent Wilson I have another question about this formula and hope you can help. I'm using your suggested formula below:

    =IF( COUNTIF(CHILDREN(Status@row), "In Progress")>0, "In Progress", IF( COUNTIF(CHILDREN(Status@row), "Complete") / COUNT(CHILDREN(Status@row)) = 1, "Complete", IF( COUNTIF(CHILDREN(Status@row), "Complete")>0, "In Progress", "")))

    However, if there's nothing in the cell, then the parent row shows "#DIVIDE BY ZERO" and if I mark any task complete it marks the parent row as COMPLETE. I'd like the parent row to remain blank if the sub-tasks are blank, and I'd like the parent row to show Complete ONLY when all sub-tasks are completed. Any thoughts?

    As always, I appreciate any sage advice you have on this!

  • Tony Fronza
    Tony Fronza ✭✭✭✭
    Options

    @Brent Wilson I need a modification on the formula you provided (see below). I want to include the N/A status in the formula to change the parent row's status to N/A if ALL sub-tasks are N/A. I tried to add the same IF(COUNT(CHILDREN(Status@row) logic, but I keep receiving a UNPARSEABLE error message.

    I would greatly appreciate your assistance!

    =IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") / COUNT(CHILDREN(Status@row)) = 1, "Complete", IF(COUNTIF(CHILDREN(Status@row), "Complete") > 0, "In Progress", "Not Started")))

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hey @Tony Fronza

    Try adding it as the first statement in your formula:

    =IF(COUNTIF(CHILDREN(Status@row), "N/A") = COUNT(CHILDREN(Status@row)), "N/A", IF(COUNTIF(CHILDREN(Status@row), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(Status@row), "Complete") / COUNT(CHILDREN(Status@row)) = 1, "Complete", IF(COUNTIF(CHILDREN(Status@row), "Complete") > 0, "In Progress", "Not Started"))))

    Cheers,

    Genevieve

  • Tony Fronza
    Tony Fronza ✭✭✭✭
    Options

    Thank you so much, @Genevieve P. That worked perfectly :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!