If(AND) across a range
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?
Comments
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!