Parent Health Status of Children Rows Based On If Certain Criteria Is Met

cyndi_walker108886

Hello,

I have a Gender column with these options:

Female, Male, Transgender, Non-binary/non-conforming, Prefer not to respond

I need the parent row to reflect a status of RED ball if there is no 'Female' represented in any of the children.

I currently have this formula, but feel I'm missing significant COUNTIF references:

=IF((CONTAINS(FEMALE)(CHILDREN)), "GREEN", "RED")

How can I course correct this formula? THANK YOU!!

Maricel Medina

If you are using hierarchy, you can do the following formula: =IF(COUNTIF(CHILDREN(), "Female") = 0, "Red")

If you are not using hierarchy and have a range, you can use:

=IF(COUNTIF(Gender10:Gender15, "Female") = 0, "Red")

cyndi_walker108886

@Maricel Medina , would this only work on the first level of hierarchy? I've got 3 levels and need it to work on the 2nd one. Also, the column was originally formatted as a drop down list - but it appears the formula won't work in a parent cell if that column is formatted as such? But even after I remove the drop down list format, it doesn't render a ball for me.

To clarify, the intent is to trigger red when there are no female candidates being considered. I'll need to write a similar formula for ethnicity as well - if there are only white candidates, then I need a red ball; otherwise a green one.

I'm using the formula (image 1), but the results are null (image 2).....

Image 1 - formula displayed:

Image 2 - no red ball:

@Andrée Starå .... don't suppose you'd take a gander at this one since I've already bugged you today? ;P