Excluding Blank Cells in Formula
I have a master row that I would like to track the year's progress. In my image,
I would like the [Status] ball to stay Green if "YES" and if blank in [Count] cells January - December. Want it to turn Red, if there is a "NO" in any [Count] cell. If all [Count] cells are blank January - December then "Blue"
YES followed by blank =Green
Any NO =Red
All blank=Blue
Best Answer
-
Yes it did. Try this for the specific row you highlighted in your example.
=IF(CONTAINS("No", count191:count202) = "True", "Red", IF(COUNT(count191:count202) = 0, "Blue", "Green"))
Once you enter it in that cell you will be able to copy the cell and paste it into your other ones. Basically if it finds any NO then its red, If all the cells are blank it will be blue and anything else will be green.
Answers
-
A screenshot of your sheet with sensitive data removed would be very helpful in understanding what you are wanting to do. Sharing any formulas you are currently using to calculate the YES and NO's might help as well.
-
I was using Explorer and that browser didn't let me upload images. :). Google Chrome is working.
-
Can you share a screenshot with column names? Its hard to tell which column is which. Thanks.
-
@Mike Wilday , did the column names help?
-
Yes it did. Try this for the specific row you highlighted in your example.
=IF(CONTAINS("No", count191:count202) = "True", "Red", IF(COUNT(count191:count202) = 0, "Blue", "Green"))
Once you enter it in that cell you will be able to copy the cell and paste it into your other ones. Basically if it finds any NO then its red, If all the cells are blank it will be blue and anything else will be green.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!