#### 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

edited 12/09/19

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!

Tags:

## Comments

• edited 03/29/17

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/2476091-frequently-asked-questions-about-using-formulas

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 non-consequetive 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 non-consecutive columns, so Craig's second solution is working.    Thank you all again!

This discussion has been closed.