包含特定文本的Countif

<\/p>

Insert a helper column on both sheets (can be hidden after setup to keep the sheet looking clean) and use this:<\/p>

=PID@row + \"//www.santa-greenland.com/community/discussion/comment/\"<\/p>

<\/p>

This will convert everything into text strings so that you have the same data type throughout. You would then use these helper columns in your formula.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-07 21:47:15","updateUser":{"userID":140332,"name":"twarner","url":"https:\/\/community.smartsheet.com\/profile\/twarner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T21:47:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":96340,"type":"question","name":"IF formula with Multiple conditions- help required","excerpt":"Hi, I am trying to work out the forumla that is based on a lead time field (manually entered) vs turnaround field (automatically calculated on dates) , the formula I want has multiple conditions which I am struggling to put into one IF formula, would appreciate anyones help - thanks\/ I want to have a formula that says: IF…","categoryID":322,"dateInserted":"2022-10-07T10:39:38+00:00","dateUpdated":null,"dateLastComment":"2022-10-07T11:27:54+00:00","insertUserID":137102,"insertUser":{"userID":137102,"name":"Laura Byrne","url":"https:\/\/community.smartsheet.com\/profile\/Laura%20Byrne","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!upsqVxYhnDs!7yMr-74eQAs!Q44B0N7isTy","dateLastActive":"2022-10-07T11:22:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-10-09T18:24:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3330283052,"url":"https:\/\/community.smartsheet.com\/discussion\/96340\/if-formula-with-multiple-conditions-help-required","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96340\/if-formula-with-multiple-conditions-help-required","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":96340,"commentID":346433,"name":"Re: IF formula with Multiple conditions- help required","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/346433#Comment_346433","dateInserted":"2022-10-07T11:27:54+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-10-09T18:24:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-07T11:25:24+00:00","dateAnswered":"2022-10-07T11:18:41+00:00","acceptedAnswers":[{"commentID":346429,"body":"

Hi @Laura Byrne<\/a> <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Turnaround Time]@row = \"//www.santa-greenland.com/community/discussion/comment/\", \"//www.santa-greenland.com/community/discussion/comment/\", IF(AND([Lead Time]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", [Turnaround Time]@row > [Lead Time]@row), \"Not Met\", IF([Turnaround Time]@row <= [Lead Time]@row, \"Met\")))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/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","log":{"dateUpdated":"2022-10-07 11:25:24","updateUser":{"userID":137102,"name":"Laura Byrne","url":"https:\/\/community.smartsheet.com\/profile\/Laura%20Byrne","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!upsqVxYhnDs!7yMr-74eQAs!Q44B0N7isTy","dateLastActive":"2022-10-07T11:22:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":96331,"type":"question","name":"Counting matches between 2 columns","excerpt":"I am trying to see which customer numbers repeat from one week to the next. I have a column for last week, then one for this week with client #'s and this formula to look for matches. =MATCH([10.7.22]@row, [9.30.22]:[9.30.22]) From the image, clearly there are numbers in 10.7 week that are in week 9.30, but coming up as…","categoryID":322,"dateInserted":"2022-10-07T00:02:07+00:00","dateUpdated":null,"dateLastComment":"2022-10-07T15:55:02+00:00","insertUserID":134005,"insertUser":{"userID":134005,"name":"Connie Torres","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Torres","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!w6vUt79SBP4!ZYtWmWN9cnQ!oc-noNSH2sl","dateLastActive":"2022-10-07T15:12:45+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":"2022-10-07T19:39:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":34,"score":null,"hot":3330261429,"url":"https:\/\/community.smartsheet.com\/discussion\/96331\/counting-matches-between-2-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96331\/counting-matches-between-2-columns","format":"Rich","lastPost":{"discussionID":96331,"commentID":346481,"name":"Re: Counting matches between 2 columns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/346481#Comment_346481","dateInserted":"2022-10-07T15:55:02+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":"2022-10-07T19:39:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-07T15:12:39+00:00","dateAnswered":"2022-10-07T13:48:02+00:00","acceptedAnswers":[{"commentID":346457,"body":"

Try using the thrid portion of the MATCH function to specify an exact match:<\/p>

=MATCH([10.7.22]@row, [9.30.22]:[9.30.22], 0<\/strong>)<\/p>"},{"commentID":346459,"body":"

I believe what you want to use is a countif formula<\/p>

=countif([9.30.22]:[9.30.22],[10.7.22]@row)<\/p>

<\/p>

Or are you trying to find the row number where it matches?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-07 15:12:40","updateUser":{"userID":134005,"name":"Connie Torres","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Torres","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!w6vUt79SBP4!ZYtWmWN9cnQ!oc-noNSH2sl","dateLastActive":"2022-10-07T15:12:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">