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:

• ✭✭✭✭✭
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

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

• ✭✭✭✭✭
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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

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

• ✭✭✭✭
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!

• ✭✭✭✭
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")))

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

• ✭✭✭✭
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!