COUNTIF/S Sheet 2 has "Auto Doors" in dropdown
Hello!
New to Smartsheet and trying to get a ticketing system going for our FY 2024. I've done a Form already and am working on a metrics sheet for a Dashboard. This is all VERY new to me and I've had no training but I got a subscription and trying to make the best of it.
I have Sheet 1 where the Form responses go to. I have Sheet 2 where I am trying to tally all my information. I am trying to count the amount of times each program is requesting a new ticket but I can't seem to get it right. Thank you!!
Best Answer
-
Victoria_Indimar ✭✭✭✭✭
Hello and welcome!
If your Program list in Sheet 2 is written out to match the option in your form and entries in Sheet 2, then you can just reference those for look up:
=COUNTIF({Task Request Range 1},=Program@row)
Where "Program" is the actual column header you have the list Auto Doors, Baler, Compactors, etc.
Answers
-
Victoria_Indimar ✭✭✭✭✭
Hello and welcome!
If your Program list in Sheet 2 is written out to match the option in your form and entries in Sheet 2, then you can just reference those for look up:
=COUNTIF({Task Request Range 1},=Program@row)
Where "Program" is the actual column header you have the list Auto Doors, Baler, Compactors, etc.
-
Thank you a million times!!!! I sat on this for HOURS and couldn't figure it out. Thanks again!
Help Article Resources
Categories
You don't need the square brackets [ ] around false in your lookup - these will definitely cause an error.<\/p>
If the parent item is listed first, it should capture this item in the lookup. If that isn't the case, you can get round this by using an INDEX\/COLLECT instead. Unfortunately I can't really say what this would look like without column headers on the respective sheets.<\/p>
Hope this helps, but if you've any problems\/questions then just post! 🙂<\/span><\/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":111578,"type":"question","name":"Help with Conditional Index Match","excerpt":"Hello, I have a sheet titled Copy\/Paste Data with a column that has multiple text values. I have another sheet titled 23-24 Status. I am trying to conduct an index match on sheet titled Copy\/Paste Data when a specific column within this sheet contain the text SNP but keep getting #UNPARSEABLE error. =IF({Copy\/Paste Data…","snippet":"Hello, I have a sheet titled Copy\/Paste Data with a column that has multiple text values. I have another sheet titled 23-24 Status. I am trying to conduct an index match on sheet…","categoryID":322,"dateInserted":"2023-10-12T14:40:30+00:00","dateUpdated":null,"dateLastComment":"2023-10-12T18:15:34+00:00","insertUserID":145651,"insertUser":{"userID":145651,"name":"MichaelO1","url":"https:\/\/community.smartsheet.com\/profile\/MichaelO1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-12T19:49:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":145651,"lastUser":{"userID":145651,"name":"MichaelO1","url":"https:\/\/community.smartsheet.com\/profile\/MichaelO1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-12T19:49:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":34,"score":null,"hot":3394258564,"url":"https:\/\/community.smartsheet.com\/discussion\/111578\/help-with-conditional-index-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111578\/help-with-conditional-index-match","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":111578,"commentID":399753,"name":"Re: Help with Conditional Index Match","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399753#Comment_399753","dateInserted":"2023-10-12T18:15:34+00:00","insertUserID":145651,"insertUser":{"userID":145651,"name":"MichaelO1","url":"https:\/\/community.smartsheet.com\/profile\/MichaelO1","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-12T19:49:16+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-10-12T18:55:08+00:00","dateAnswered":"2023-10-12T17:50:18+00:00","acceptedAnswers":[{"commentID":399736,"body":" It looks like you are going to need an INDEX\/COLLECT instead. Try this:<\/p> =INDEX(COLLECT({Copy\/Paste Data Range 2}, {Copy\/Paste Data Range 3}, @cell = CEID@row, {Copy\/Paste Data Range 1}, @cell = \"SNP\"), 1)<\/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":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]},{"discussionID":111571,"type":"question","name":"Not counting based on multiple factors","excerpt":"I'm building a formula that uses the contents in a cell to effect a percentage. Basically: =(count all cells within a range with the word \"PASS\") (divide this number by) (All the cells within same range that are NOT blank ) The piece of the formula that looks at the row and avoids blank cells looks like this:…","snippet":"I'm building a formula that uses the contents in a cell to effect a percentage. Basically: =(count all cells within a range with the word \"PASS\") (divide this number by) (All the…","categoryID":322,"dateInserted":"2023-10-12T13:35:09+00:00","dateUpdated":null,"dateLastComment":"2023-10-12T17:25:08+00:00","insertUserID":147576,"insertUser":{"userID":147576,"name":"jblunda","url":"https:\/\/community.smartsheet.com\/profile\/jblunda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pafraWu5tDM!w6fq9-v6BDM!XIcicgnpuVa","dateLastActive":"2023-10-12T17:21:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":147576,"lastUser":{"userID":147576,"name":"jblunda","url":"https:\/\/community.smartsheet.com\/profile\/jblunda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pafraWu5tDM!w6fq9-v6BDM!XIcicgnpuVa","dateLastActive":"2023-10-12T17:21:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":32,"score":null,"hot":3394251617,"url":"https:\/\/community.smartsheet.com\/discussion\/111571\/not-counting-based-on-multiple-factors","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111571\/not-counting-based-on-multiple-factors","format":"Rich","tagIDs":[254,537],"lastPost":{"discussionID":111571,"commentID":399732,"name":"Re: Not counting based on multiple factors","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/399732#Comment_399732","dateInserted":"2023-10-12T17:25:08+00:00","insertUserID":147576,"insertUser":{"userID":147576,"name":"jblunda","url":"https:\/\/community.smartsheet.com\/profile\/jblunda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pafraWu5tDM!w6fq9-v6BDM!XIcicgnpuVa","dateLastActive":"2023-10-12T17:21:11+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-10-12T17:23:29+00:00","dateAnswered":"2023-10-12T14:46:35+00:00","acceptedAnswers":[{"commentID":399687,"body":"
