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.

Formula for a dependancy

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.
    Robert S. Employee



    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
    J. Craig Williams ✭✭✭✭✭✭


    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.


  • 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.
    Robert S. Employee
    edited 12/08/17



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

This discussion has been closed.