Counting checkboxes

Alfonzo Angulo de la Cruz

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

 

 

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)