Reference the contents of an entire column and return a RYG value

paul.vellacott54306

Hi,

I am trying to return a RED value (traffic light)  to a specific cell where any cell in the column "Risk Flag" has been checked. I can get it to return a Red value where a single specified cell in the Risk Flag column is flagged - as follows:

=IF([Risk Flag]2 = "True", "Red")

I need it to return a Red value where any cell in the column is flagged.

I would be grateful for any help.

Cheers

Paul

 

Andrée Starå

Hi Paul,

Try this.

=IF(COUNTIF([Risk Flag]:[Risk Flag]; 1) > 0; "Red")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

I think you forgot the rest of the IF. 

 

=IF(COUNTIF([Risk Flag]:[Risk Flag], 1) > 0, "Red")

Andrée Starå

Oops! That was for one specific cell. Thanks!

I'll update!

paul.vellacott54306

Thank you both. Much appreciated.

Andrée Starå

Happy to help!

Have a fantastic day!

Best,

Andrée

paul.vellacott54306

Hi Paul,

 

Could you assist me further by showing me how it would be structured to show a Red when a Flag is check and Green when no flag is checked?

Cheers

Paul

Andrée Starå

Hi Paul,

Try this,

=IF(COUNTIF([Risk Flag]:[Risk Flag], 1), "Red", "Green")

I hope this helps you!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

paul.vellacott54306

Brilliant... Thank you Andreé

Andrée Starå

I'm always happy to help!

Have a fantastic weekend!

Best,

Andrée

paul.vellacott54306

Hi Andree,

Sorry to bother you again. The function you provided me:

=IF(COUNTIF([Risk Flag]:[Risk Flag], 1), "Red", "Green")

works perfectly when on one Risk Flag is checked. If more than one flag is checked - which does occur - i get the following message: 

#INVALID DATA TYPE.

Are you able to help me with that?

 

Cheers

Paul

Paul Newcome

Expand on the "logical statement" potion of the IF statement.

 

=IF(COUNTIF([Risk Flag]:[Risk Flag], 1) > 0, "Red", "Green")

Chris Egge

Simialrly, I am trying to stuff the flag COUNTIF in front of the below RYG automation, such that, if the flag is "Ticked/Selected", I get a resut of "Red", but if not "Ticked/Selected", the balance of the automation runs adn returns other results of "Green" or "Blue"....Getting #UNPARSSABLE, any tips?

=IF(COUNTIF([At Risk]@row, 1) > 0, "Red"), IF([% Complete]@row < 0.2, "Red", IF([% Complete]@row < 0.99, "Green", "Blue"))

Thanks in advance,

Chris

Chris Egge

All~ Had an "aha..." moment, and got the above request sorted, had an extra ")".... after the flag statement and needed to close the argument with ")"... Classic..

Corrected below:

=IF(COUNTIF([At Risk]@row, 1) > 0, "Red", IF([% Complete]@row < 0.2, "Red", IF([% Complete]@row < 0.99, "Green", "Blue")))