Parent row status formula based on child rows

Samantha Baruah

Roll up formula for status

I have this formula (shown below) where I am trying to get the status of the parent row automatically entered based on the child row. I'm not sure if I've stared at this too long and my eyes are crossing, but it's not doing what I want it to do. I think I've listed all the permutations below.

1. If the child rows are all blank, than return a blank for parent row

2. If any of the child rows are in progress than return in progress for parent row

3. If all child rows are X than return X for parent row

4. Treat n/a as blank unless all child rows are n/a

5. If child rows are a combination of any value than return in progress for parent row

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

Genevieve P.

Hey @Samantha Baruah

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.

Blank cells aren't counted in a COUNT function so lets start with your first statement:

=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

Genevieve P.

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

Genevieve P.

Hey @Samantha Baruah

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.

Blank cells aren't counted in a COUNT function so lets start with your first statement:

=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

Lisa Wood

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!

Genevieve P.

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

Lisa Wood

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

Genevieve P.

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?

Lisa Wood

Yes, exactly. :)

Thanks, @Genevieve P. !

Genevieve P.

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

Samantha Baruah

@Genevieve P.

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

MarkCep

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

Genevieve P.

Hey @MarkCep

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

https://community.smartsheet.com/discussion/112852/calculating-of-yeses-in-child-rows-from-yes-no-column

Let me know if you still need help!

Cheers,

Genevieve

Kelly Dawson

@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

Kelly Dawson

@Genevieve P. 

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

Genevieve P.

Hey @Kelly Dawson

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

Kelly Dawson

@Genevieve P.

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.