If/Then formula for symbols based on range?
Hello,
I'm collecting responses from multiple departments for the same question. I've added the question to a parent row, and each department is a child row below it. Status is a ball symbol...red=issue, yellow=in progress, and green=complete...and every row has a symbol.
I'd like to create a formula on the parent row based on the child rows. So, if any child row has a red ball, the parent row will have a red ball. If child rows have no red but at least one yellow, the parent row will have a yellow status. And finally, the parent row will only show green status if ALL child rows are green.
I'm not sure where to even start with this formula! Any help is appreciated :)
Thank you!
Best Answer
-
You're welcome! I'm glad I could help 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Dana G
You can use the CHILDREN function to COUNT how many of the Child rows have a certain colour, then display the correct colour in the Parent row. Try something like this:
=IF(COUNTIF(CHILDREN(), "Red") >= 1, "Red", IF(COUNTIF(CHILDREN(), "Yellow") >= 1, "Yellow", "Green"))
See: COUNTIF Function / CHILDREN Function / IF Function
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you @Genevieve P. ! This formula is so elegant :) it worked perfectly.
-
You're welcome! I'm glad I could help 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!