Select RYG color based on other cells

Holly D

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!

Andrew DeCounter

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"))

Mike Wilday

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")))

J. Craig Williams

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

Mike Wilday

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

Holly D

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!