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

Hello,

 

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

 

 

Tags:

Comments

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

    Regardless,

     

    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?

     

    Craig

     

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