Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Select RYG color based on other cells
I have a sheet with checkboxes in 3 columns, and I'd like to have an RYG symbol in a separate cell be red if 1 box is checked, yellow if 2 and green if 3. If no boxes are checked I'd like to have no RYG symbol visible. I am assuming this needs to be some combination of IF and COUNTIF, but I have tried different combinations and appealed to Smartsheet itself and just can't figure this out. Do you guys have any suggestions? Thanks!
Comments

Holly,
I recommend having a look at this help article which outlines how to write formulas to interact with the different column types: https://help.smartsheet.com/articles/2476091frequentlyaskedquestionsaboutusingformulas
For example:
=IF(Value1 > 7, "Red", IF(Value1 > 3, "Yellow", "Green"))

Assuming your checkbox columns are called checkboxcolumn1, checkboxcolumn2, and checkboxcolumn3, then you could paste the following formula into your RGB symbol formatted column in row 1 and it should do the trick.
=IF(countifs([checkboxcolumn1]1,1,[checkboxcolumn2]1,1,[checkboxcolumn3]1,1) = 1, "Red", IF(countifs([checkboxcolumn1]1,1,[checkboxcolumn2]1,1,[checkboxcolumn3]1,1) = 2, "Yellow", IF(countifs([checkboxcolumn1]1,1,[checkboxcolumn2]1,1,[checkboxcolumn3]1,1) = 3, "Green")))

Andrew (and Mike)
Mike's solution does not work.
All of the COUNTIFS in his formula will return 0 or 1. COUNTIFS is not counting each of the check boxes, it is counting how many of a range is matching all of the criteria.
Because the range is only one cell, the return will be either 0 or 1.
So, let's first change the solution if the columns are consequetive:
=IF(COUNTIFS([checkboxcolumn1]1:[checkboxcolumn3]1, 1) = 1, "Red", IF(COUNTIFS([checkboxcolumn1]1:[checkboxcolumn3]1, 1) = 2, "Yellow", IF(COUNTIFS([checkboxcolumn1]1:[checkboxcolumn3]1, 1) = 3, "Green")))
This works because now the range is 3 cells.
The nonconsequetive solution is ugly as each COUNTIFS in Mike's formula needs to be replaced by 3 COUNTIF's
IF((COUNTIF(cell1, 1) + COUNTIF(cell2,1) + COUNTIF(cell3,1))=1,"Red", ...and so on.
Craig

Good Catch Craig! I should test my theories before I type them!

Thank you so much! I think I went through all these steps at some point, got as far as the 'consecutive' solution and stalled! The checkboxes are in nonconsecutive columns, so Craig's second solution is working. Thank you all again!
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives