If(AND) across a range

Tim Kuykendall

I have a hierarchy of checkboxes.  I am trying create a IF(AND) formula to at the Level 1 checkbox to be checked once all the Level 2 boxes are checked.  I am able to make it work when I reference each of the cells , for example:  =IF(AND([Map Complete]18= 1, [Map Complete]19 = 1, [Map Complete]20 = 1), 1, 0).  When I do it this way and I add a new row, it doesn’t know to that I’ve added. 

 

I tried using reference: =IF(AND([Map Complete]18:[Map Complete]20 = 1), 1, 0) but I get an error of “#INVALID OPERATION”.

 

Also, can you add formulas to Checkbox column types?  When I tried I got an error of “#BOLLEAN EXPECTED”

 

Any suggestions?

L_123

you can use any type of formula in any type of column*. The only thing that is limited is the output. for example a date column must always output a date.

Your main issue in the last formula is the use of the AND. Instead of using and, try a countif. =IF(Countif([Map Complete]18:[Map Complete]20, 1) = 3,1,0) or something like that.

 

*quick edit: Except for contact columns. Those are screwy.

Paul Newcome

Give this a try...

 

=IF(COUNTIFS(CHILDREN(), 1) = COUNT(CHILDREN()), 1)

 

This will count all of the children rows that are checked, and if that equals the total count of children rows (all of them are checked), then it will check your parent box. Otherwise the parent box will remain unchecked. I use this A LOT.