公式和函数

=IF(COUNTIFS({Number}, @cell = [ID Number]@row) = 1, INDEX({Location}, MATCH([ID Number]@row, {Number}, 0)), IF(COUNTIFS({Number}, @cell = [ID Number]@row) = 0, \"Error\", \"Both\"))<\/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":104331,"type":"question","name":"Formula help - Getting Unparseable!","excerpt":"In my formula, I am taking data from three columns 1)if my Room Type field = King and it falls in between the 2) hotel check-in and 3) hotel check-out date to give me a 1. I need to know how many Queen Rooms I need on any given day. Here is my formula: =([# of King Rooms Needed]@row = \"King\") * IF(F(AND(DATE(2023, 7, 29)…","categoryID":322,"dateInserted":"2023-04-21T17:56:14+00:00","dateUpdated":"2023-04-21T18:19:07+00:00","dateLastComment":"2023-04-21T20:58:51+00:00","insertUserID":81751,"insertUser":{"userID":81751,"name":"Lori Leighton","url":"https:\/\/community.smartsheet.com\/profile\/Lori%20Leighton","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-49ft9BEInw!4cDYOE1TTNY!weLUSUrkXuD","dateLastActive":"2023-04-21T19:18:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":91566,"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-04-21T21:08:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3364212305,"url":"https:\/\/community.smartsheet.com\/discussion\/104331\/formula-help-getting-unparseable","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/104331\/formula-help-getting-unparseable","format":"Rich","lastPost":{"discussionID":104331,"commentID":372923,"name":"Re: Formula help - Getting Unparseable!","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/372923#Comment_372923","dateInserted":"2023-04-21T20:58:51+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-04-21T21:08:38+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":"2023-04-21T19:45:59+00:00","dateAnswered":"2023-04-21T19:16:57+00:00","acceptedAnswers":[{"commentID":372884,"body":"

Are you able to provide a screenshot for reference?<\/p>

<\/p>

It seems like you might need something along the lines of:<\/p>

=IF(AND([Room Type]@row = \"King\", [Hotel Arrival Date]@row<= DATE(2023, 7, 29), [Hotel Departure Date]@row>= DATE(2023, 7, 29)), 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":[]},{"discussionID":104332,"type":"question","name":"COUNTIF with HAS or COUNTM for Report","excerpt":"I'm making modifications to the marketing request template set. There is a report that ties back to the intake form. The intake report overtime adds a count of 1 to the assignee for each project completed by the assignee for each month. The issue I am having is, if there are multiple people assigned to the filed the count…","categoryID":322,"dateInserted":"2023-04-21T17:56:26+00:00","dateUpdated":"2023-04-21T18:19:09+00:00","dateLastComment":"2023-04-21T19:18:43+00:00","insertUserID":160521,"insertUser":{"userID":160521,"name":"Daniel Martinez","url":"https:\/\/community.smartsheet.com\/profile\/Daniel%20Martinez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-04-21T19:41:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-04-21T21:08:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":16,"score":null,"hot":3364205109,"url":"https:\/\/community.smartsheet.com\/discussion\/104332\/countif-with-has-or-countm-for-report","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/104332\/countif-with-has-or-countm-for-report","format":"Rich","lastPost":{"discussionID":104332,"commentID":372885,"name":"Re: COUNTIF with HAS or COUNTM for Report","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/372885#Comment_372885","dateInserted":"2023-04-21T19:18:43+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-04-21T21:08:38+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":"2023-04-21T19:41:05+00:00","dateAnswered":"2023-04-21T19:18:43+00:00","acceptedAnswers":[{"commentID":372885,"body":"

I don't think you need the IF statement. This should be all you need:<\/p>

<\/p>

=COUNTIFS({IntakeStatus}, $Status@row, {IntakeAssignedTo}, HAS(@cell, $[Primary Column]@row), {IntakeCompleteMonth}, Jan$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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">