IF COUNTIFS Formula for Multiple Status Circles

anna2121

Hello,

I currently have this formula: =IF(COUNTIFS(KPIs31:KPIs36, "Red") > 0, "Red", IF(COUNTIFS(KPIs31:KPIs36, "Yellow") > 0, "Yellow", "Green")) in the Parent row. This takes the underlying status circles (children) within the parent so if there's at least one Red, it results in Red in the parent cell. If there's at least one yellow, results in yellow. However, now I want to incorporate the Gray status circle into the formula (Gray means Not Started for status) where if there's at least 4 Gray, it should result in Grey overall in that respective column like KPIs and Value. How would I add this on? Welcome any feedback to improve this as well to better capture potential mixes of status circles. Thank you!

Krissia B.

Hello @anna2121

Thank you for your post! In reviewing & testing your formula, I revised your formula to something below.

For KPIs

=IF(COUNTIFS(KPIs31:KPIs36, "Gray") > 0, "Gray", IF(COUNTIFS(KPIs31:KPIs36, "Red") > 0, "Red", IF(COUNTIFS(KPIs31:KPIs36, "Yellow") > 0, "Green")))

For Value

=IF(COUNTIFS(Value31:Value36, "Gray") > 0, "Gray", IF(COUNTIFS(Value31:Value36, "Red") > 0, "Red", IF(COUNTIFS(Value31:Value36, "Yellow") > 0, "Green")))

Hope this helps!

Cheers~

Krissia

Paul Newcome

I would suggest this:

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

This formula will work across every single column without modification and will take into account any new child rows that are added or if any child rows are later removed.