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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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"))))))))
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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!
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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
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?
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Yes, exactly. :)
Thanks, @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"))))))))
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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?
Hey @MarkCep
It looks like you posted your question and received a solution here:
Let me know if you still need help!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
@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
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
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")))))
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
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.
