IF/AND/OR Formula for Parent Row

Tony Fronza

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

Brent Wilson

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

Genevieve P.

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

Brent Wilson

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

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

Tony Fronza

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

Tony Fronza

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

Tony Fronza

@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

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

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

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