IF COUNTIFS Formula for Multiple Status Circles

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!

smartsheet 10.4 question.PNG


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!