New User Help - formulas and ranges

MarkH · 01/04/19

Hi,

I'll try to describe in detail what I want to do and would appreciate any help you can provide.

I have a range of cells that show Red,Yellow or Green Balls and this is used to check the status of each stage in a project.

I would like to also have a cell at the top level of the project that displays RED if there are any Red balls in the range, Yellow if there are no red but some yellow or green, and Green if there are only green balls.

So there is a hierarchy from red to yellow to green, but if there is even one of the higher level colour, the result should be that.

I'm not very experienced with formulas and started with a simple IF and COUNTIF approach but it doesn't seem to work and is also limited to one colour.

Can anyone tell me how this could be done?

Thanks

Mark

Brian W · 01/04/19

You can use COUNTIF to see if the number of Red or Yellow balls is greater than 0, and nest those inside an IF statement to return "Red" or "Yellow" accordingly:

=IF(COUNTIF([RYGColumn]2:[RYGColumn]30, "Red") > 0, "Red", IF(COUNTIF([RYGColumn]2:[RYGColumn]30, "Yellow") > 0, "Yellow", "Green”))

Keep in mind, you can nest IF statements to check for multiple conditions and return different responses.

New User Help - formulas and ranges

Comments

Help Article Resources

Categories