I want to get a nested formula counting children colors but I'm missing What I'm doing wrong

Gambrit

I'm not an expert on formulas , so I took this one from a different post I found which I believe cover my needs

=IF(COUNTIFS(CHILDREN(), "Red") > 0, "Red", IF(COUNTIFS(CHILDREN(), "Yellow") >= COUNTIFS(CHILDREN(), "Green"), "Yellow", "Green"))

The thing I need to do is to count if there is at least one color Red, mark it as Red, if none, keep looking at Yellow and set it as Yellow, if none of the above set it as Green

When I validate the formula, it is only returning "Green"

Appreciate if someone can point me to the right direction.

Carson Penticuff

CHILDREN() looks ONLY one level down from your current row. All dots on your two "2nd level" rows are green, meaning the parent will always be green. You can either:

1. Use your existing formula, but also copy it to your second level entries as well. The second level entries will then calculate based on their children, and then those results will roll up to the top level.
2. Replace all CHILDREN() references in your formula with DESCENDANTS(). DESCENDANTS looks at all levels below current.

Either of these will work, but will give you different results.