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

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

Find more posts tagged with

Formulas

Comments

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")))

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.