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, pleaseVisit the Current Forums.
Counting Check Boxes
I'm trying to get a formula to count the number of Check Boxes that are checked in all the Children rows under a Parent row.
When using ="Count:" + COUNTIF(CHILDREN(), 1), which is one of the formula examples I'll get this Count: 2
All I'm looking for is the number so I tried =COUNTIF(CHILDREN(), 1) but it doesn't work. All I get back is #1 or 0 EXPECTED.
Any help would be appreciated. Thanks.
Comments
-
Brett Evans ✭✭✭✭✭✭
MVigrass,
Click on a cell to see the formula
https://app.smartsheet.com/b/publish?EQBCT=636085f90ff64e05910d078bf2191f60
This is definitely doable.
-
MVigrass ✭
Thanks for that. I was looking at that type of formula too, but was hoping to be able to do the count within the same column at the parent level to keep things simple.
-
Travis EmployeeMVigrass, when you add a formula to a checkbox cell, the cell is expecting a 1 or 0 as a result (1 = checked 0= unchecked) if the result is a number. Your first formula worked because the result was formatted as as text string and not a number (because you had "Count:" +). Use this trick to format the result as a text string but show only the number in the parent row:
=COUNTIF(CHILDREN(), 1) + ""
-
PLorimer ✭
Thanks Travis. This is the trick I've been looking for.
Categories
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109500,"type":"question","name":"Auto-populate emails on a \"contact list\" column","excerpt":"I have a Form created for internal employees where they can submit request tickets to my department. We all have an account with Smartsheet. One of our columns (\"Requestor Name\") is in the Form where they type in their names. The \"Requestor Name\" column is a Contac List column. My issue here is, how can I have Smartsheet…","snippet":"I have a Form created for internal employees where they can submit request tickets to my department. We all have an account with Smartsheet. One of our columns (\"Requestor Name\")…","categoryID":321,"dateInserted":"2023-08-25T21:43:36+00:00","dateUpdated":"2023-08-25T21:44:14+00:00","dateLastComment":"2023-08-25T22:14:15+00:00","insertUserID":164850,"insertUser":{"userID":164850,"name":"ocanel","title":"Analyst","url":"https:\/\/community.smartsheet.com\/profile\/ocanel","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-27T06:55:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":164850,"lastUserID":165374,"lastUser":{"userID":165374,"name":"Shanky Paul","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Shanky%20Paul","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcAdE60g-MBEj1rR28GVz9zn6RmbZsmEQm5EiEV=s96-c","dateLastActive":"2023-08-26T21:35:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":23,"score":null,"hot":3386002071,"url":"https:\/\/community.smartsheet.com\/discussion\/109500\/auto-populate-emails-on-a-contact-list-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109500\/auto-populate-emails-on-a-contact-list-column","format":"Rich","tagIDs":[204,247,334,448,527],"lastPost":{"discussionID":109500,"commentID":392677,"name":"Re: Auto-populate emails on a \"contact list\" column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392677#Comment_392677","dateInserted":"2023-08-25T22:14:15+00:00","insertUserID":165374,"insertUser":{"userID":165374,"name":"Shanky Paul","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Shanky%20Paul","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtcAdE60g-MBEj1rR28GVz9zn6RmbZsmEQm5EiEV=s96-c","dateLastActive":"2023-08-26T21:35:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-27T06:55:03+00:00","dateAnswered":"2023-08-25T22:14:15+00:00","acceptedAnswers":[{"commentID":392677,"body":"
Hi, <\/p>
Add a \"Created By\" column in your sheet and turn on \"Required smartsheet login to access your form\" in your form setting. So that whoever fills out the form, their email id will be automatically captured in \"created by\" column. This will replace your \"Requestor Name\" column. <\/p>
Thank you!<\/p>
<\/p>
![\"Screen](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/8138SXSFVML6\/screen-shot-2023-08-26-at-3-36-45-am.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
