Archived 2016 Posts

Archived 2016 Posts

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

  • ✭✭✭✭✭✭

    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.

Trending Posts