Formula for Multi-Select Contact Column and Dropdown List
On my sheet I have one column titled “Inspectors”, this is a multi-select contact column that will have multiple options in it at times. I have a second column titled "Categories for Failure" this is a dropdown list which will also have multiple options selected at times.
I was hoping to create a formula that uniquely tracks individual failures by individual inspectors.
Currently when I have two contacts in the multi-select contact column it will not track on my data sheet, only when there is a single selection will it track.
My current formula is: =COUNTIFS({Categories for Failure}, CONTAINS("Writing on Unit", @cell), {Quality Inspector}, Inspector@row)
EX: Multi-Select Contact Column “Inspectors” has “Multiple Unique Names” Multi-Select Column “Categories for Failures” has “Multiple Unique Criteria”
Totaling the unique categories for failure by individual inspectors using a multi-select contact column and a dropdown list..
Hopefully this makes a bit of sense and thank you for any help you can provide!
Best Answer
-
Paul Newcome ✭✭✭✭✭✭You don't need the AND function. The syntax from your original formula was correct. You just need to swap the CONTAINS for the HAS (proper syntax of course) and use that function for both criteria pieces.
HAS(@cell, "text string")
Answers
-
Paul Newcome ✭✭✭✭✭✭
You will need the HAS function for both criteria sets as opposed to the CONTAINS function.
-
Matthew R ✭✭Thank you!
This was the formula I was trying with the HAS function involved, it is giving me an UNPARSEABLE error. I am thinking my structure is off a bit or maybe incorporating the AND function incorrectly.
-
Paul Newcome ✭✭✭✭✭✭
You don't need the AND function. The syntax from your original formula was correct. You just need to swap the CONTAINS for the HAS (proper syntax of course) and use that function for both criteria pieces.
HAS(@cell, "text string")
-
Matthew R ✭✭
YOU WERE 100% SPOT ON, formula below works perfectly for what I am after.
=COUNTIFS({Categories for Failure}, HAS(@cell, "Missing Parts"), {Inspector}, HAS(@cell, "Tony Mannon"))
Can not thank you enough, a little tweaking last night and head scratching and we are good to go, proper syntax is difficult for me still haha!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Hi @BristolCVN<\/a> <\/p> I hope you're well and safe!<\/p> You must add\/change the Rule #1 name.<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":443,"urlcode":"dynamic-view","name":"Dynamic View"}]},{"discussionID":108759,"type":"question","name":"Help with formula","excerpt":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the number of dropdown items from. This is the sheet that i want the formula written in to return an output. Currently I have =IF(HAS({centers},…","snippet":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the…","categoryID":322,"dateInserted":"2023-08-09T20:13:45+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T17:17:45+00:00","insertUserID":161673,"insertUser":{"userID":161673,"name":"Lauren Hughes","url":"https:\/\/community.smartsheet.com\/profile\/Lauren%20Hughes","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WSHqXkuSMHM!nddov_PnFbs!Fw3jLOwOmG0","dateLastActive":"2023-08-10T17:37:14+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-10T17:25:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3383301690,"url":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108759,"commentID":390080,"name":"Re: Help with formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390080#Comment_390080","dateInserted":"2023-08-10T17:17:45+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-10T17:25:14+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/CM7LO30OUR2Q\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T17:16:38+00:00","dateAnswered":"2023-08-09T21:23:23+00:00","acceptedAnswers":[{"commentID":389908,"body":" Try this:<\/p> =COUNTM(COLLECT({July}, {Centers}, HAS(@cell, Center@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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"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":[]}">
