# Parent row status formula based on child rows

Options

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

Options

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

Options

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

Options

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

• ✭✭✭✭
edited 10/18/23
Options

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!

Options

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

• ✭✭✭✭
edited 10/18/23
Options

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

Options

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?

• ✭✭✭✭
Options

Yes, exactly. :)

Thanks, @Genevieve P. !

Options

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

• edited 10/18/23
Options

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

• ✭✭✭✭
Options

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

Options

Hey @MarkCep

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

Let me know if you still need help!

Cheers,

Genevieve

• ✭✭✭✭
Options

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

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

Options

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

• ✭✭✭✭
Options

Thank you I had continued to play with it after posting the question, as it is i my nature to investigate a and play, and I came to that realization as well.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!