公式帮助

嗨,团队,

你能建议我如何写一个公式,可以捕获所有客户名称由“,”分开,这是反对红色状态。

需要注意的是,状态可以定期更新,因此今天可能只有2个客户端,而tmrw可能多或少。

你能帮

image.png


最佳答案

  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    @Gaurav Chauhan

    是的!这个公式与上面的公式类似。然而,看起来Status列是单选的,所以您可以简单地引用“Status”列作为您的标准,而不是使用HAS。

    例如:

    =JOIN(COLLECT({客户端名称},{状态},[项目状态]@row), ", ")

    让我知道这是否为您工作,或者如果您想看到屏幕截图。

    欢呼,

    吉纳维芙

答案

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭

    它是这样写的吗:

    =JOIN(COLLECT({客户端名称},{状态},HAS([项目状态]@row), ", "))

  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    @Gaurav Chauhan

    是的!这个公式与上面的公式类似。然而,看起来Status列是单选的,所以您可以简单地引用“Status”列作为您的标准,而不是使用HAS。

    例如:

    =JOIN(COLLECT({客户端名称},{状态},[项目状态]@row), ", ")

    让我知道这是否为您工作,或者如果您想看到屏幕截图。

    欢呼,

    吉纳维芙

帮助文章资源欧宝体育app官方888

Insert an auto-number column (called \"auto\" in this example).<\/p>


<\/p>

Then insert a text\/number column and use this column formula:<\/p>

=MATCH(Auto@row, Auto:Auto, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-08-27 18:56:53","updateUser":{"userID":148626,"name":"Berto D","url":"https:\/\/community.smartsheet.com\/profile\/Berto%20D","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5dfZ-Xnfdb8!5EtYuWXiYHU!F1_6oGulnb-","dateLastActive":"2022-08-27T20:33:48+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":94748,"type":"question","name":"COUNTIFS: Column Formula pulling from 2 Columns on 2 Sheets","excerpt":"Hello! I've built some COUNTIFS in the past and I've always struggled, but I've also always managed to get it in the end. I'm trying to build a column formula that pulls data from 4 different columns between two different sheets. What I've come up with so far is coming back with UNPARSEABLE. =COUNTIFS({Reviewer 1},…","categoryID":322,"dateInserted":"2022-08-26T16:31:18+00:00","dateUpdated":null,"dateLastComment":"2022-08-26T18:12:33+00:00","insertUserID":134306,"insertUser":{"userID":134306,"name":"Jacob Gress","url":"https:\/\/community.smartsheet.com\/profile\/Jacob%20Gress","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJwqBvuWxIiPSD2vcmIenDzc3Ih8GZWwZ_cMVms=s96-c","dateLastActive":"2022-08-26T18:12:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":134306,"lastUser":{"userID":134306,"name":"Jacob Gress","url":"https:\/\/community.smartsheet.com\/profile\/Jacob%20Gress","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJwqBvuWxIiPSD2vcmIenDzc3Ih8GZWwZ_cMVms=s96-c","dateLastActive":"2022-08-26T18:12:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3323070831,"url":"https:\/\/community.smartsheet.com\/discussion\/94748\/countifs-column-formula-pulling-from-2-columns-on-2-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/94748\/countifs-column-formula-pulling-from-2-columns-on-2-sheets","format":"Rich","lastPost":{"discussionID":94748,"commentID":341432,"name":"Re: COUNTIFS: Column Formula pulling from 2 Columns on 2 Sheets","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/341432#Comment_341432","dateInserted":"2022-08-26T18:12:33+00:00","insertUserID":134306,"insertUser":{"userID":134306,"name":"Jacob Gress","url":"https:\/\/community.smartsheet.com\/profile\/Jacob%20Gress","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJwqBvuWxIiPSD2vcmIenDzc3Ih8GZWwZ_cMVms=s96-c","dateLastActive":"2022-08-26T18:12:47+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-08-26T18:12:45+00:00","dateAnswered":"2022-08-26T18:12:33+00:00","acceptedAnswers":[{"commentID":341432,"body":"

I was finally able to get it to work (across multiple sheets). I think the trick was just changing it from an \"OR\" to a \"+\" and treating them all as their own formula in one long strand<\/p>


<\/p>

=COUNTIFS({Reviewer 1}, Associate@row) + COUNTIFS({Project Reviews Range 2}, Associate@row) + COUNTIFS({Project Reviews Archive Range 1}, Associate@row) + COUNTIFS({Project Reviews Archive Range 2}, Associate@row)<\/p>

1st Sheet: =COUNTIFS({Reviewer 1}, Associate@row) + COUNTIFS({Project Reviews Range 2}, Associate@row)<\/p>

2nd Sheet: + COUNTIFS({Project Reviews Archive Range 1}, Associate@row) + COUNTIFS({Project Reviews Archive Range 2}, Associate@row)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-08-26 18:12:45","updateUser":{"userID":134306,"name":"Jacob Gress","url":"https:\/\/community.smartsheet.com\/profile\/Jacob%20Gress","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJwqBvuWxIiPSD2vcmIenDzc3Ih8GZWwZ_cMVms=s96-c","dateLastActive":"2022-08-26T18:12:47+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":[]},{"discussionID":94674,"type":"question","name":"Is it possible to match multiple values with in a single cell?","excerpt":"I'm sorry if this is long winded. I am creating a workflow process where a file will need to be emailed to multiple users at different locations. There is a drop down that lists the locations, in this example it's 3 separate locations, Portland, Minnesota, and Houston. I'd like to keep this drop down to allow multiple…","categoryID":322,"dateInserted":"2022-08-25T16:00:36+00:00","dateUpdated":null,"dateLastComment":"2022-08-26T19:48:46+00:00","insertUserID":132751,"insertUser":{"userID":132751,"name":"John Kaler","url":"https:\/\/community.smartsheet.com\/profile\/John%20Kaler","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!c8jb5620xSY!5D9YuVHXa_w!9nRFda38UZt","dateLastActive":"2022-08-26T19:58:26+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-08-26T19:38:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":65,"score":null,"hot":3322992562,"url":"https:\/\/community.smartsheet.com\/discussion\/94674\/is-it-possible-to-match-multiple-values-with-in-a-single-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/94674\/is-it-possible-to-match-multiple-values-with-in-a-single-cell","format":"Rich","tagIDs":[254,334],"lastPost":{"discussionID":94674,"commentID":341454,"name":"Re: Is it possible to match multiple values with in a single cell?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/341454#Comment_341454","dateInserted":"2022-08-26T19:48:46+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-08-26T19:38:33+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/C33TOGY5Z1NC\/2022-08-25-9-51-47.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"2022-08-25_9-51-47.jpg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-08-26T13:11:03+00:00","dateAnswered":"2022-08-26T12:55:07+00:00","acceptedAnswers":[{"commentID":341324,"body":"

It is not currently possible to use a formula to populate multiple usable contacts within a single cell.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-08-26 13:11:03","updateUser":{"userID":132751,"name":"John Kaler","url":"https:\/\/community.smartsheet.com\/profile\/John%20Kaler","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!c8jb5620xSY!5D9YuVHXa_w!9nRFda38UZt","dateLastActive":"2022-08-26T19:58:26+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"},{"tagID":334,"urlcode":"automations","name":"Automations"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">