Formula for a dependancy

Ann Alexandrowicz

I have 5 columns on a sheet that all have check boxes. Is there a formula that will say If these 5 boxes are checked then they have the status of green dot?

Robert S.

Hello,

 

If you'd like for a Status column to be a Green ball only if all 5 checkboxes are checked, there are a few different ways that a formula could be written to do this. The smallest formula would look something like this:

 

=IF(COUNTIF([Check1]1:[Check5]1, 1) = 5, "Green")

 

This counts the checkboxes that are checked in row 1 for the columns "Check1" through "Check5", and if it equals 5 returns "Green". If this is placed in a Symbols type column with the RYG balls chosen, a green ball will show only when all 5 checkboxes are set. If you have a different number of checkboxes, you can change the 5 to whatever number needed.

 

As I mentioned above there are other ways to write this formula, but this is the most compact and efficient way.

J. Craig Williams

Ann,

Also be aware that Robert's assumption is that the 5 columns are contiguous and/or there are are no intervening Checkbox type columns that might be checked and no Text/Number type columns that might contain a value equal to 1.

Craig

Ann Alexandrowicz

Thank You. 



Second Question



Is it possible to build onto that formula that if something is unchecked it would be red?



So If all checked green, if unchecked red?



 

Robert S.

Hello,

 

Craig is absolutely right, about the assumption made in my previous formula. Thanks Craig, I should have specified. For this formula to work how it is, the checkbox columns must be contiguous.

 

As for the status being red unless all checkboxes are checked, you can add a value if false to the end so that it looks something like this:

 

=IF(COUNTIF([Check1]1:[Check5]1, 1) = 5, "Green", "Red")

 

Here's our help center article with more information on the structure of an IF() function (https://help.smartsheet.com/function/if).