Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
IF Formulas for Status Columns
Hello,
I'm looking for assistance in writing an IF formula to change a cell's value based on the value of other cells in the same column. Specifically, the values will be symbols. If all symbols are green, I'd like the first row to show as green, if any are yellow, then the top row should be yellow, and lastly if any are red the top row should be red.
Would the best way to achieve these results be to write an IF formula?
Thanks in advanced!
Comments
-
Hi,
In my example, I've assumed that the top row is a parent.
Try something like this.
=IF(COUNTIF(CHILDREN(); "Green") = COUNT(CHILDREN()); "Green"; IF(COUNTIF(CHILDREN(); "Red") > 0; "Red"; IF(COUNTIF(CHILDREN(); "Yellow") > 0; "Yellow")))
The same version but with the below changes for your and others convenience.
=IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow")))
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it help/work?
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Making the same assumption as Andree, here is a second solution...
.
=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", "Green"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
