IF COUNTIFS Formula for Multiple Status Circles

Options

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!


Answers

  • Krissia B.
    Krissia B. Moderator
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!