New User Help - formulas and ranges
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
Comments
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!