交叉表引用/“分配给”允许多个联系人每个单元格喜欢公式?

jhawker
jhawker
编辑12/09/19 在公式与函数

党卫军社区-我需要帮助。我已经为我的销售团队，销售阶段等创建了一个销售管道表，这基本上是它自己的CRM。我试图获得衡量标准，以保持销售团队成员的诚实，并向领导团队报告真相。

挑战:交叉表格公式(如下)提取正确的数据，但当ASSIGNED TO列选中“每个单元格允许多个联系人”时，该公式忽略了搜索的联系人。现在的数字是扭曲的。

问题:我想把公式拉到分配给(即使当多个联系人存在)的所有行拉丹尼尔所以-包含或喜欢是需要的。有人知道正确的公式吗?

=COUNTIFS({销售管道范围3}，="已联系"，{销售管道范围代表}，"Danielle")

标签:
· 在Facebook上分享 在推特上分享

评论

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

在Smartsheet中创建和编辑公式

交叉表参考的公式组合

Smartsheet功能列表

类别

想直接在Smartsheet中练习使用公式吗?

请查看公式手册模板!
@deb_63_hydracor<\/a> Yes, that makes sense that it worked without the OR. In the formula with the \"OR\" you placed it in the range parameter and that is not a valid range.<\/p>

Try this:<\/p>

=COUNTIFS({Tier}, OR(@cell = \"Experiencia Lite\". @cell = \"Experiencia\", @cell = \"Experiencia Plus Unlimited\"), {Renewal Month}, 1, {Renewal Year}, 2023)<\/p>

By the way, I'm not 100% sure the OR function will work like that, but try it. If not, the alternative is to simply have 3 COUNTIFS that add together. Each COUNTIFS will look for a different \"Experiencia\".<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-04 21:23:07","updateUser":{"userID":141331,"name":"deb_63_hydracor","url":"https:\/\/community.smartsheet.com\/profile\/deb_63_hydracor","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJzYn23TqUOGU8A4PW0HalCwzLK5YNQsVxHwYcgL=s96-c","dateLastActive":"2023-01-04T21:20:21+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":99217,"type":"question","name":"Countifs from one sheet with multiple categories","excerpt":"I need to complete a countifs formula for data in one sheet with multiple categories (screenshots of data needed). The columns that need to be counted have different drop-down options. I have to count active status defects the total (New, open, Assigned, Ready for Retest, Assigned to Vendor, Reopened) for each module. The…","categoryID":322,"dateInserted":"2023-01-03T18:57:51+00:00","dateUpdated":null,"dateLastComment":"2023-01-04T19:38:48+00:00","insertUserID":156209,"insertUser":{"userID":156209,"name":"Jasna","url":"https:\/\/community.smartsheet.com\/profile\/Jasna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-04T18:52:52+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-01-04T19:41:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":31,"score":null,"hot":3345635199,"url":"https:\/\/community.smartsheet.com\/discussion\/99217\/countifs-from-one-sheet-with-multiple-categories","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99217\/countifs-from-one-sheet-with-multiple-categories","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":99217,"commentID":355898,"name":"Re: Countifs from one sheet with multiple categories","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/355898#Comment_355898","dateInserted":"2023-01-04T19:38:48+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-01-04T19:41:03+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\/HX5FVEIJYK3K\/capture1-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture1.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-01-04T19:17:49+00:00","dateAnswered":"2023-01-04T14:59:32+00:00","acceptedAnswers":[{"commentID":355833,"body":"

Try retyping your last one in Smartsheet itself, here in the Community, or in a text editor (such as Notepad), but do NOT use programs such as Word.<\/p>


<\/p>

Take a look at these quotes: \" \" \" \" \" \" \" \" <\/p>


<\/p>

See how they are straight up and down whereas yours are slanted? Those slanted ones are called \"smart quotes\" which (ironically enough) are not recognized as valid characters in Smartsheet. Try replacing all of them with the ones that are straight up and down (using one of the methods above), and then we can take it form there if it is still not working.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-04 19:17:49","updateUser":{"userID":91566,"name":"Genevieve P.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/T4YLN045Q7TF\/n81H11L2YPG9T.jpg","dateLastActive":"2023-01-04T21:47:50+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}}},"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":99224,"type":"question","name":"[FORMS] URL Query string not working","excerpt":"Any idea what could cause it not to work? When I add the query ?Property=ABC it just refreshes with only the URL. It is just working on this one form but not on the others. What do I need to check? I've already read the documentation. Re creating another form on this sheet does not help. Here is the link, but please DO NOT…","categoryID":322,"dateInserted":"2023-01-03T19:57:05+00:00","dateUpdated":"2023-01-04T12:51:47+00:00","dateLastComment":"2023-01-04T14:35:56+00:00","insertUserID":139461,"insertUser":{"userID":139461,"name":"heyjay","url":"https:\/\/community.smartsheet.com\/profile\/heyjay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-04T21:46:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":91566,"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":"2023-01-04T21:05:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":53,"score":null,"hot":3345624181,"url":"https:\/\/community.smartsheet.com\/discussion\/99224\/forms-url-query-string-not-working","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99224\/forms-url-query-string-not-working","format":"Rich","tagIDs":[204],"lastPost":{"discussionID":99224,"commentID":355819,"name":"Re: [FORMS] URL Query string not working","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/355819#Comment_355819","dateInserted":"2023-01-04T14:35:56+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":"2023-01-04T21:05:48+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-01-04T17:04:36+00:00","dateAnswered":"2023-01-03T20:57:56+00:00","acceptedAnswers":[{"commentID":355759,"body":"

@heyjay<\/a> <\/p>

It works for me.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-04 17:04:36","updateUser":{"userID":139461,"name":"heyjay","url":"https:\/\/community.smartsheet.com\/profile\/heyjay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-04T21:46:36+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":204,"urlcode":"Forms","name":"Forms"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">