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.

Counting checkboxes




I am counting the children of selected check boxes and then seting a ball color with formula bellow:


=IF(COUNTIF(CHILDREN(Comp15:Comp17), 0) = 2, "", IF(COUNTIF(CHILDREN(Comp15:Comp17), 0) = 1, "Red", IF(COUNTIF(CHILDREN(Comp15:Comp17), 0) = 0, "Green")))


This works fine but, when I have more than three checkboxes the formula gets complicated.  


Is there a way to evaluate the following case:


if the result of Comp25:Comp35 = 0 the ball is Green, if the result of Comp25:Comp35 = 10 there is no ball, but if the result of Comp25:Comp35 is different than 0 or 10 the ball is red.


Thank in advance





  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    That works? I would have said it wouldn't.

    Testing shows it always green, unless I  copied it wrong.



    Pardon me but let me clarify your first formula


    3 checkboxes

    - if 3 are checked, green

    - if 2 are checked, red,

    - else blank


    (you are counting the unchecked ones and I'm in non-work mode - so I might be confused)


    now you want to expand on that to a row 11 rows (Comp25:Comp35) is eleven rows, not 10. I'm going to assume that is just an oversight.


    and what you want is

    for N number of checkboxes (as you've discovered, restricting yourself to three got you here in the first place*)


    - if all are checked, green

    - if none are checked, blank,

    - else red


    Is that is correctly clarifying things -- here it is:


    =IF(COUNTIF(CHILDREN(Comp40), 1) = 0, "Red", IF(COUNTIF(CHILDREN(Comp40), 1) = COUNT(CHILDREN(Comp40)), "Green", ""))


    or more legibly:


    =IF(COUNTIF(CHILDREN(Comp23), 1) = 0, "", IF(COUNTIF(CHILDREN(Comp23), 1) = COUNT(CHILDREN(Comp23)), "Green", "Red"))


    for row 23

    so - check for 0 check boxes checked - blank

    then check if they are all checked (by comparing the checked ones with the total count of them) - green

    otherwise red.


    Does that work?




    * I've been known to take short cuts - why overcomplicate things on a simple 3 check box problem? - but as soon as I need to modify (because of a bug or changing requirements), I try to catch more edge cases to avoid me having to come back and solve the same problem again.

    Expanding your formula from 3 to 11 (or 10) puts me in the mode of "N checkboxes" instead of 11 (or 10)






  • That definitely helped!


    Thank you



This discussion has been closed.