IF AND CHILDREN CONTAINS

Sylvia Elizabeth

Hi,

I want to create a formula where if the children contains a status "Entregado Parcialmente" or "Sin entregar" to turn the parent row into "Entregado Parcialmente" ; otherwise if all the children contains or met the status "Terminado" turn the parent row into "Terminado", but if a cell doesn't contain anything I just want it to stay in blank.

I have made a formula but it doesn´t work, can somebody help me?. I would appreciate it a lot.

Thank you!

Sylvia E.

Paul Newcome

Ok. Let's give this one a go...

=IF(COUNTIFS(CHILDREN(); "") = COUNT(CHILDREN(Compromiso@row)); ""; IF(OR(CONTAINS("Entregado Parcialmente"; CHILDREN()); CONTAINS("Sin entregar"; CHILDREN())); "Entregado Parcialmente"; IF(COUNTIFS(CHILDREN(); "Terminado") = COUNT(CHILDREN()); "Terminado")))

David Joyeuse

Hi @Sylvia Elizabeth

We can notice that some of your children rows do have a "N/A" value within them. Are they of any importance for your parent row? I mean, if all are N/A do you want your parent row to return something? Or if there's only one N/A? Or should they just be left out of the formula?

That can change the formula quite a bit :)

Sylvia Elizabeth

Hi David.

No, it doesn't matter if there's a N/A in the row, just the identify if the other conditions are met, we can skip the N/A.

Thanks! :)

Sylvia Elizabeth

Hi @David Joyeuse !

Could you find any solution? :( , or any other ideas?

Thank you!

Paul Newcome

Try somethign like this...

=IF(OR(CONTAINS("Entregado Parcialmente", CHILDREN()), CONTAINS("Sin entregar", CHILDREN())), "Entregado Parcialmente", IF(COUNTIFS(CHILDREN(), "Terminado") = COUNT(CHILDREN()), "Terminado"))

Sylvia Elizabeth

Hi Paul!

Thanks!, it worked verywell, but it stills drop me "Terminado" even if all the cells are in blank, I would rather that if all the cells are in blank just to stay in blank.. here and example if I put that formula even if cells are empty.

Paul Newcome

Ok. Try this one...

=IF(COUNTIFS(CHILDREN(), "") = COUNT(CHILDREN()), "", IF(OR(CONTAINS("Entregado Parcialmente", CHILDREN()), CONTAINS("Sin entregar", CHILDREN())), "Entregado Parcialmente", IF(COUNTIFS(CHILDREN(), "Terminado") = COUNT(CHILDREN()), "Terminado")))

Sylvia Elizabeth

Hi Paul,

It still remains in "Terminado" the parent row. I tried to change a little bit the formula like the example below and the cell changes to blank, but then it stops working with the other statuses.

=IF(COUNTIFS(CHILDREN(); "") = ""; IF(OR(CONTAINS("Entregado Parcialmente"; CHILDREN()); CONTAINS("Sin entregar"; CHILDREN())); "Entregado Parcialmente"; IF(COUNTIFS(CHILDREN(); "Terminado") = COUNT(CHILDREN()); "Terminado")))

Paul Newcome

Ok. Let's give this one a go...

=IF(COUNTIFS(CHILDREN(); "") = COUNT(CHILDREN(Compromiso@row)); ""; IF(OR(CONTAINS("Entregado Parcialmente"; CHILDREN()); CONTAINS("Sin entregar"; CHILDREN())); "Entregado Parcialmente"; IF(COUNTIFS(CHILDREN(); "Terminado") = COUNT(CHILDREN()); "Terminado")))

Sylvia Elizabeth

Excelent!! It worked perfectly thank you Paul.

Regards!

Paul Newcome

Happy to help. 👍️