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!

Find more posts tagged with

Sheets

Formulas

Comments

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!