Excluding Blank Cells in Formula

Alishia Topper

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

Mike Wilday

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.

Mike Wilday

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.

Alishia Topper

Exclude blank months.jpg

Alishia Topper

I was using Explorer and that browser didn't let me upload images. :). Google Chrome is working.

Mike Wilday

Can you share a screenshot with column names? Its hard to tell which column is which. Thanks.

Alishia Topper

Alishia Topper

@Mike Wilday , did the column names help?

Mike Wilday

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.