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



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:


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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!