"Status" column to reflect 2nd column input for "Cancelled or On Hold"

jbonetti

I have a Status column that is calculated via an IF statement referencing two other columns, [% Complete] and [Cancelled / On Hold].

=IF([% Complete]@row = "", "", IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started"))))

I would like the roll-up status of the parent to say "Cancelled / On Hold" if all Status fields are "Cancelled" or "On Hold" instead of "In Progress" as it does now.

Is that possible and what would I need to add to my formula to do so?

Thank you kindly.

Genevieve P.

Hi @jbonetti

Try the following:

=IF([% Complete]@row = "", "", IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN([Cancelled / On Hold]@row), "Cancelled") + COUNTIF(CHILDREN([Cancelled / On Hold]@row), "On Hold") = COUNT(CHILDREN([Task Name]@row)), "Cancelled / On Hold"), IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))

I'll break it down:

=IF([% Complete]@row = "", "",

This is the same as your current formula. Then we get into the question of hierarchy:

IF(COUNT(CHILDREN([Task Name]@row)) > 0,

If there are Children below this row (checking the Task Name to identify the number of children, since blank cells wouldn't be counted)

Then, if the COUNT of "Cancelled" and "On Hold" is the SAME as the Count of ALL Children, return "Cancelled / On Hold"

IF(COUNTIF(CHILDREN([Cancelled / On Hold]@row), "Cancelled") + COUNTIF(CHILDREN([Cancelled / On Hold]@row), "On Hold")

=

COUNT(CHILDREN([Task Name]@row)), "Cancelled / On Hold"),

Finally, otherwise, the rest of your formula.

IF([Cancelled / On Hold]@row <> "", [Cancelled / On Hold]@row, IF([% Complete]@row = 1, "Complete", IF([% Complete]@row > 0, "In Progress", "Not Started")))))

Cheers!

Genevieve