# Parent row status formula based on child rows

### =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIF(CHILDREN(), "Blocked") = COUNT(CHILDREN()), "Blocked", IF(COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Canceled", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "Not Started", IF(COUNTIF(CHILDREN(), "N/A") > 0, "In Progress"))))))))

• Employee

Nested IFs look at statements one at a time, stopping at a first one that meets the criteria. I actually think your numbered list is great! We may just need to swap a few things around.

`=IF(COUNT(CHILDREN()) = 0, "",`

Then we can do your In Progress statement

`IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress",`

However then you have a note that "N/A" should count as "blank", which I gather to mean those rows need to be skipped... so if you have 2 Children and 1 is "Complete" and 1 is "N/A", then it should say "Complete". Is that correct? If so, we'll need to add that criteria into all of your statements.

First, we can look for if "N/A" is the only thing in the Child Rows:

`IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A",`

Now we need to add that in your criteria for all the other statuses:

`IF(COUNTIF(CHILDREN(), "Complete") = COUNTIF(CHILDREN(), <> "N/A"), "Complete", `

`IF(COUNTIF(CHILDREN(), "On Hold") = COUNTIF(CHILDREN(), <> "N/A"), "On Hold", `

`IF(COUNTIF(CHILDREN(), "Blocked") = COUNTIF(CHILDREN(), <> "N/A"), "Blocked", `

`IF(COUNTIF(CHILDREN(), "Canceled") = COUNTIF(CHILDREN(), <> "N/A"), "Canceled", `

`IF(COUNTIF(CHILDREN(), "Not Started") = COUNTIF(CHILDREN(), <> "N/A"), "Not Started"`

Let me know if this new formula works for you and meets all your criteria!

`=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A", IF(COUNTIF(CHILDREN(), "Complete") = COUNTIF(CHILDREN(), <> "N/A"), "Complete", IF(COUNTIF(CHILDREN(), "On Hold") = COUNTIF(CHILDREN(), <> "N/A"), "On Hold", IF(COUNTIF(CHILDREN(), "Blocked") = COUNTIF(CHILDREN(), <> "N/A"), "Blocked", IF(COUNTIF(CHILDREN(), "Canceled") = COUNTIF(CHILDREN(), <> "N/A"), "Canceled", IF(COUNTIF(CHILDREN(), "Not Started") = COUNTIF(CHILDREN(), <> "N/A"), "Not Started"))))))))`

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee

Great! That's an easy change, just ad "In Progress" at the end of the formula as the default to go to if it doesn't meet any of the other criteria:

=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A", IF(COUNTIF(CHILDREN(), "Complete") = COUNTIF(CHILDREN(), <>"N/A"), "Complete", IF(COUNTIF(CHILDREN(), "On Hold") = COUNTIF(CHILDREN(), <>"N/A"), "On Hold", IF(COUNTIF(CHILDREN(), "Blocked") = COUNTIF(CHILDREN(), <>"N/A"), "Blocked", IF(COUNTIF(CHILDREN(), "Canceled") = COUNTIF(CHILDREN(), <>"N/A"), "Canceled", IF(COUNTIF(CHILDREN(), "Not Started") = COUNTIF(CHILDREN(), <>"N/A"), "Not Started", "In Progress"))))))))

October 8 - 10, Seattle, WA | Register now

• Employee

Nested IFs look at statements one at a time, stopping at a first one that meets the criteria. I actually think your numbered list is great! We may just need to swap a few things around.

`=IF(COUNT(CHILDREN()) = 0, "",`

Then we can do your In Progress statement

`IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress",`

However then you have a note that "N/A" should count as "blank", which I gather to mean those rows need to be skipped... so if you have 2 Children and 1 is "Complete" and 1 is "N/A", then it should say "Complete". Is that correct? If so, we'll need to add that criteria into all of your statements.

First, we can look for if "N/A" is the only thing in the Child Rows:

`IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A",`

Now we need to add that in your criteria for all the other statuses:

`IF(COUNTIF(CHILDREN(), "Complete") = COUNTIF(CHILDREN(), <> "N/A"), "Complete", `

`IF(COUNTIF(CHILDREN(), "On Hold") = COUNTIF(CHILDREN(), <> "N/A"), "On Hold", `

`IF(COUNTIF(CHILDREN(), "Blocked") = COUNTIF(CHILDREN(), <> "N/A"), "Blocked", `

`IF(COUNTIF(CHILDREN(), "Canceled") = COUNTIF(CHILDREN(), <> "N/A"), "Canceled", `

`IF(COUNTIF(CHILDREN(), "Not Started") = COUNTIF(CHILDREN(), <> "N/A"), "Not Started"`

Let me know if this new formula works for you and meets all your criteria!

`=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A", IF(COUNTIF(CHILDREN(), "Complete") = COUNTIF(CHILDREN(), <> "N/A"), "Complete", IF(COUNTIF(CHILDREN(), "On Hold") = COUNTIF(CHILDREN(), <> "N/A"), "On Hold", IF(COUNTIF(CHILDREN(), "Blocked") = COUNTIF(CHILDREN(), <> "N/A"), "Blocked", IF(COUNTIF(CHILDREN(), "Canceled") = COUNTIF(CHILDREN(), <> "N/A"), "Canceled", IF(COUNTIF(CHILDREN(), "Not Started") = COUNTIF(CHILDREN(), <> "N/A"), "Not Started"))))))))`

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
edited 10/18/23

Hi @Genevieve P. - How would this formula be different if the parent was marked complete only once all the children were complete? I'm looking to make that adjustment. Thanks!

• Employee

Hey @Lisa Wood

The current formula above should do this! It will say it's Complete as long as all the COUNT of all the children that say "Complete" is the same as the COUNT of all the Children that are not N/A.

Keep in mind that you'll need to have some sort of value in every cell, since blank cells are skipped when counting. You'll need to make sure all the children say "Not Started" if they haven't begun yet.

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
edited 10/18/23

Ah, @Genevieve P. that makes perfect sense - thanks so much for your speedy response!

The issue now is that the parent row is blank when all children say "not started" and one shows "complete"....thoughts?

Cheers!

Lisa

• Employee

Hey @Lisa Wood

What do you want to appear in that instance? This formula is built to be blank if it doesn't meet any of the predetermined criteria (meaning there's a mix of different statuses). Are you wanting the default to be "In Progress" if there's a mix?

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭

Yes, exactly. :)

Thanks, @Genevieve P. !

• Employee

Great! That's an easy change, just ad "In Progress" at the end of the formula as the default to go to if it doesn't meet any of the other criteria:

=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A", IF(COUNTIF(CHILDREN(), "Complete") = COUNTIF(CHILDREN(), <>"N/A"), "Complete", IF(COUNTIF(CHILDREN(), "On Hold") = COUNTIF(CHILDREN(), <>"N/A"), "On Hold", IF(COUNTIF(CHILDREN(), "Blocked") = COUNTIF(CHILDREN(), <>"N/A"), "Blocked", IF(COUNTIF(CHILDREN(), "Canceled") = COUNTIF(CHILDREN(), <>"N/A"), "Canceled", IF(COUNTIF(CHILDREN(), "Not Started") = COUNTIF(CHILDREN(), <>"N/A"), "Not Started", "In Progress"))))))))

October 8 - 10, Seattle, WA | Register now

• edited 10/18/23

Thank you so much for this. You are a god sent!

with the addition of the "In Progress" at the end it is exactly what I wanted!!!

• ✭✭✭✭✭

This very helpful. If you take this same idea and want to apply % (of completed) of the child rows, how would the formula look?

• Employee

Hey @MarkCep

It looks like you posted your question and received a solution here:

Let me know if you still need help!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭

@Genevieve P. I was looking for a formula to do what this formula is doing, however when I use it modified for my statuses I am not getting the return I would like to see, can you help me modify it.

I am looking for the Parent to return a status of in Process if the Children are in process, or in queue, on hold if Children On Hold than on Hold, and complete if all Children are complete. Below is how I modified the formula from above

=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "On Hold"), "On Hold", IF(COUNTIF(CHILDREN(), "In Queue Started"), "In Queue", "In Process"))))

It is retuning In Process even when all Children have complete.

Thank you for the help

• ✭✭✭✭
edited 12/04/23

After posting my comment below,

I continued to play the formula nad I was able to get a return on the parent line for on hold if one child had on hold in the Status, however if more than one child has the same value "in Queue", "In Progress" I am getting an Invalid Data error retuned, how would I fix this

=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "Hold") > 0, "Hold", IF(COUNTIF(CHILDREN(), "In Progress"), "In Progress", IF(COUNTIF(CHILDREN(), "In Queue"), "In Queue", IF(COUNTIF(CHILDREN(), "Hold"), "On Hold", "Complete")))))

Thank you again for the help

Kelly

• Employee

You're close! 🙂

Once you get to "In Progress" there's no statement to say what you're looking for after the COUNT. You're looking to see if the Count is greater than 0

=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIF(CHILDREN(), "Hold") > 0, "Hold", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "In Queue") > 0, "In Queue", IF(COUNTIF(CHILDREN(), "Hold") > 0, "On Hold", "Complete")))))