How do I make a nested IF formula check another cell as Priority to change status of another cell?

Robert Tebault

Hello, let me start off by saying any assistance available is greatly appreciated!!!!

In our work schedule we use parent child hierarchy. Through the use of other community discussions, I found a formula that I could modify and use that would change the status of the parent row based on the overall statuses of the children rows. This is helpful and works (copy of working formula below) however there is a gotcha with it and we are stuck trying to get the formula modified to work correctly. The way we need it to work is when the row containing "MSI-Installation" is marked a status of "Cancelled" the parent row becomes "Cancelled" regardless of the statuses in the other rows, but if it is not "cancelled then follow the rest of the formula. (Screen shot of sheet below for example)

Any ideas how to modify the below working formula to use the "MSI-Installation" column as a priority or qualifier if you will?

WORKING Formula:

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "Not Needed", @cell = "Cancelled", @cell = "Canceled")) = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Not Needed", @cell = "Cancelled", @cell = "Canceled")) = COUNT(CHILDREN()), "Completed", "In Progress"))))

jmyzk_cloudsmart_jp

Hi @Robert Tebault

I suggest adding a helper column that combines the status column and the Multiple column.😀

For example, MSI-Installation Cancel Check

=Status@row + Multiple@row

Using this helper column, you can add the following if clause before the current formula.

=IF(COUNTIF(CHILDREN([MSI-Installation Cancel Check]@row), "CancelledMSI-Installation") > 0, "Cancell", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "Not Needed", @cell = "Cancelled", @cell = "Canceled")) = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Not Needed", @cell = "Cancelled", @cell = "Canceled")) = COUNT(CHILDREN()), "Completed", "In Progress")))))

You can open the published demo sheet below and check how the formula works by changing the status or Multiple values.

https://app.smartsheet.com/b/publish?EQBCT=9c5dcfae78914652b5619d72a5af50fb

KPH

Hi @Robert Tebault

This might be a little long winded, and you may be able to combine a couple of steps, but here are my thoughts.

(I posted at the same time as jmyzk_cloudsmart_jp and now having seen his reply, realize you have MSI-Installation in the Multiple column and we don't need to extract it from the first column - the redactions threw me! - which does make it easier. I will leave my response for anyone looking to build a formula with text contained within a child cell among other text).

1. Add a helper column (that you can hide) to identify the rows that have "MSI-Installation" in them and make this a column formula.

=IF(CONTAINS("MSI-Installation", [Primary Column]@row), "yes", "no")

2. Add a second helper column (that you can also hide) to count the number of children rows that have MSI-Installation in them and are cancelled.

=COUNTIFS(CHILDREN([In Progress]@row), "Cancelled", CHILDREN([Find MSI-Installation]@row), "yes")

3. Make the very first thing in your Parent Progress formula a check to see if "Child Cancelled" is 0. If it is 0 then your formula can be used, and if it is not "0" then it will put in "Cancelled"

=IF([Child Cancelled]@row = "0", (IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Not Started", @cell = "Not Needed", @cell = "Cancelled", @cell = "Canceled")) = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell = "Completed", @cell = "Not Needed", @cell = "Cancelled", @cell = "Canceled")) = COUNT(CHILDREN()), "Completed", "In Progress"))))), "Cancelled")