Identifying a ticked checkbox in an =IF formula

Paul R
Paul R
edited 10/27/22 inFormulas and Functions

Hi there,

I'd like to use an =IF formula to identify those cells where the checkbox is ticked - does anyone know what syntax should be used to identify a ticked checkbox, i.e. =IF(cell = ????,1,0) (where ???? = ticked checkbox)?

Thanks,

Paul

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
\"//www.santa-greenland.com/community/discussion/11536/\"),…","snippet":"I have a perplexing question, that I am sure someone knows and I am missing here. I am working on a sheet to gather data from a form. However, this form will get quite lengthily…","categoryID":322,"dateInserted":"2023-08-23T21:21:49+00:00","dateUpdated":null,"dateLastComment":"2023-08-23T21:49:44+00:00","insertUserID":163519,"insertUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-08-23T21:50:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163519,"lastUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-08-23T21:50:07+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3385654293,"url":"https:\/\/community.smartsheet.com\/discussion\/109369\/indexing-and-validating","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109369\/indexing-and-validating","format":"Rich","lastPost":{"discussionID":109369,"commentID":392247,"name":"Re: INDEXING and Validating","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392247#Comment_392247","dateInserted":"2023-08-23T21:49:44+00:00","insertUserID":163519,"insertUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-08-23T21:50:07+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-23T21:50:04+00:00","dateAnswered":"2023-08-23T21:43:36+00:00","acceptedAnswers":[{"commentID":392246,"body":"

@Frank Hammond<\/a> <\/p>

Your formula is close, looks like you just need commas between your criterion range and criterion.<\/p>

=INDEX(COLLECT({Test Sheet 1 Range 1}, {Test Sheet 1 Range 2}, = 9, {Test Sheet 1 Range 3}, = 2023), Index@row)<\/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":109367,"type":"question","name":"Number of weeks countdown from today to start date","excerpt":"Hi! I've searched other threads and haven't found a formula that works for me. I'm trying to create a countdown in weeks for events on a sheet to pull into a dashboard. I want to display the number of weeks from today's date to the event start date. I tried this formula: =WEEKNUMBER(TODAY()) - WEEKNUMBER([Start Date]12)…","snippet":"Hi! I've searched other threads and haven't found a formula that works for me. I'm trying to create a countdown in weeks for events on a sheet to pull into a dashboard. I want to…","categoryID":322,"dateInserted":"2023-08-23T20:21:42+00:00","dateUpdated":null,"dateLastComment":"2023-08-23T21:20:16+00:00","insertUserID":165582,"insertUser":{"userID":165582,"name":"Brittany Sanders","url":"https:\/\/community.smartsheet.com\/profile\/Brittany%20Sanders","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!0ljZv02FeZY!hFFbofG46Xw!7ZO-b0uofYq","dateLastActive":"2023-08-23T21:47:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-24T01:30:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":26,"score":null,"hot":3385650718,"url":"https:\/\/community.smartsheet.com\/discussion\/109367\/number-of-weeks-countdown-from-today-to-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109367\/number-of-weeks-countdown-from-today-to-start-date","format":"Rich","tagIDs":[254,292],"lastPost":{"discussionID":109367,"commentID":392241,"name":"Re: Number of weeks countdown from today to start date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392241#Comment_392241","dateInserted":"2023-08-23T21:20:16+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-24T01:30:37+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-23T21:47:20+00:00","dateAnswered":"2023-08-23T20:36:16+00:00","acceptedAnswers":[{"commentID":392228,"body":"

Try something like this:<\/p>

=(TODAY() - [Start Date]@row) \/ 7<\/p>"},{"commentID":392233,"body":"

Try flipping the first part around so that you are subtracting today from the start date.<\/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"},{"tagID":292,"urlcode":"dashboards","name":"Dashboards"}]},{"discussionID":109358,"type":"question","name":"Need help calculating the average of a column based on multiple criteria.","excerpt":"I am attempting to average the number of days to complete Action Items based on multiple criteria. I am using the =AVG(COLLECT) formula below but it is not calculating correctly. Any suggestions? =AVG(COLLECT({5S& Safety Observations Range 3}, {5S& Safety Observations Range 1}, $Jan$1), {5S& Safety Observations Month…","snippet":"I am attempting to average the number of days to complete Action Items based on multiple criteria. I am using the =AVG(COLLECT) formula below but it is not calculating correctly.…","categoryID":322,"dateInserted":"2023-08-23T18:16:16+00:00","dateUpdated":null,"dateLastComment":"2023-08-23T20:31:26+00:00","insertUserID":163745,"insertUser":{"userID":163745,"name":"dminor764","title":"CI Manager","url":"https:\/\/community.smartsheet.com\/profile\/dminor764","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-23T20:30:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163745,"lastUser":{"userID":163745,"name":"dminor764","title":"CI Manager","url":"https:\/\/community.smartsheet.com\/profile\/dminor764","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-23T20:30:54+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":30,"score":null,"hot":3385639062,"url":"https:\/\/community.smartsheet.com\/discussion\/109358\/need-help-calculating-the-average-of-a-column-based-on-multiple-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109358\/need-help-calculating-the-average-of-a-column-based-on-multiple-criteria","format":"Rich","lastPost":{"discussionID":109358,"commentID":392224,"name":"Re: Need help calculating the average of a column based on multiple criteria.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392224#Comment_392224","dateInserted":"2023-08-23T20:31:26+00:00","insertUserID":163745,"insertUser":{"userID":163745,"name":"dminor764","title":"CI Manager","url":"https:\/\/community.smartsheet.com\/profile\/dminor764","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-23T20:30:54+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-23T20:31:33+00:00","dateAnswered":"2023-08-23T18:56:27+00:00","acceptedAnswers":[{"commentID":392194,"body":"

@dminor764<\/a> are you getting any errors? <\/p>

=AVG(COLLECT({5S& Safety Observations Range 3}, {5S& Safety Observations Range 1}, $Jan$1, {5S& Safety Observations Month Name}, $Jan26, {5S& Safety Observations Range 6}, $[Primary Column]@row))<\/p>

looks like you may have had an extra parentheses, try the above<\/p>"},{"commentID":392224,"body":"

Yes, that worked 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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions