RYG Ball Automation - Calculate Program RYG Health based on Project Health

lauren.laio22986

Hi all,

I'm looking for a formula to auto-calculate the health of a program, based on 4 components within each of the 4 projects that make up that program. Photo is below of how I have the projects & components set up, along with their RYG indication. You can see they "total up" at the bottom in Overall Program. Can anyone help with a formula here?

My qualifiers for each component's health are:  

Green - Majority of components are Green

Yellow - 2+ components are Amber

Red - 1+ components are Red

I want to be able to calculate the RYG health of each component within the program, AND for those components to total up to overall program health, using the same logic listed above. Any suggestions?

Leibel S

@lauren.laio22986

General formula would be:

I assumes the names of your columns... Fix as needed

=IF(COUNTIF([RYG]$1:[RYG]$24,"Red",[Component]$1:[Component]$24,[Component]@row)>0,"Red", IF(COUNTIF([RYG]$1:[RYG]$24,"Yellow",[Component]$1:[Component]$24,[Component]@row)>1,"Yellow","Green"))

lauren.laio22986

@Leibel S thank you so much for your help! I'm getting closer, but am still getting an #INCORRECT error, and I can't figure out what I'm doing wrong. Another screenshot is below, with more info. Any suggestions?

Leibel S

My bad, change the COUNTIF to COUNTIFS

lauren.laio22986

@Leibel S YOU ARE A GENIUS! Thank you so much for this!!