Replacing false value with certain symbol or " "
Hi! I'm currently working on this project where I will roll-up my overall status using symbols. But I want the parent cell to appear either Gray or just blank if majority of the child cells are blank. Since the child cells are from another sheet where I have Not Started as blank.
Tried this formula but it still appear as Green even when all child are blank.
=IFERROR(IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Yellow") = COUNT(CHILDREN()), "Yellow"))), "Gray")
Answers
-
Hey @Farhana
Currently all the cells below are blank, which means the count will be 0. Since there are 0 green status symbols, then that means the two counts = the same thing, which is why you're seeing Green.
Try adding this statement in the front:
IF(COUNT(CHILDREN()) = 0, "",
=IFERROR(IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Yellow") = COUNT(CHILDREN()), "Yellow")))), "Gray")
Keep in mind that if you have a mixture of colours this will then show you a Gray symbol since none of them will equal the total.
It will also show you a Yellow, Red, or Green if only one or two are filled in, but they're the same colour. Is this what you would like? If not, you would want to compare the count of colours to the count of text in the column next to it:
=IF(COUNT(CHILDREN()) = 0, "", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN([Primary Column]@row)), "Green", IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN([Primary Column]@row)), "Red", IF(COUNTIFS(CHILDREN(), "Yellow") = COUNT(CHILDREN([Primary Column]@row)), "Yellow", "Gray"))))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Yes yes that works! Except that I'm removing the Gray part. But thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!