下拉多选择计数-如何计数时，超过一个下拉呈现?

darrink
darrink ✭
在公式与函数

我试图使用一个countifs，以创建一个总结的东西，使用一个值从一列(有一个单一的选择下拉)作为限定符来计数行，如果另一列有一个特定的值，“dropdownvalue_2”。

我遇到的问题是，当我在多选列中有多个值时，结果它找不到匹配，因为那里有两个值(从下拉菜单中选择一个或多个)。为了正确地计数，它必须包含“dropdownvalue_2”，我不关心它是否也有其他值，如“dropdownvalue_1”和“dropdownvalue_3”。

=COUNTIFS([first_column]:[first_column]， "status"， [second_column]:[second_column]， "dropdownvalue_2")

有什么解决办法吗?

答案

  • 杰夫瑞斯曼
    杰夫瑞斯曼 ✭✭✭✭✭

    @darrink答案在HAS函数中。(HAS的帮助页面很糟糕，我已经尝试了几个月让Smartsheet更新它。)

    HAS设计用于多选择单元。它将从包含一个或多个选择的多选单元格中为单个值找到精确匹配。

    =COUNTIFS([first_column]:[first_column]， "status"， [second_column]:[second_column]， HAS(@cell = "dropdownvalue_2"))

    问候,

    杰夫瑞斯曼，IT业务分析师和项目协调员，三菱电机特灵美国

    链接:Smartsheet功能帮助页面链接:智能表公式错误消息

  • darrink
    darrink ✭

    谢谢杰夫……我把它放进去，看起来我可能还遗漏了一些东西。

    =COUNTIFS([引用验证步骤]:[引用验证步骤]，"已批准/已验证"，[符合条件的交易类型]:[符合条件的交易类型]，HAS(cell = "托管服务(建议&报价)"))

    我得到#无法解析

  • 杰夫瑞斯曼
    杰夫瑞斯曼 ✭✭✭✭✭
    编辑05/03/22

    @darrink

    您错过了cell=前面的“@”

    =COUNTIFS([引用验证步骤]:[引用验证步骤]，"已批准/已验证"，[符合条件的交易类型]:[符合条件的交易类型]，HAS(@cell = "托管服务(提议和引用)"))

    问候,

    杰夫瑞斯曼，IT业务分析师和项目协调员，三菱电机特灵美国

    链接:Smartsheet功能帮助页面链接:智能表公式错误消息

  • darrink
    darrink ✭

    谢谢杰夫。我现在得到#不正确的参数集

  • 杰夫瑞斯曼
    杰夫瑞斯曼 ✭✭✭✭✭
    Thanks Jeff. I now get #INCORRECT ARGUMENT SET<\/p>","bodyRaw":"[{\"insert\":\"Thanks Jeff. I now get #INCORRECT ARGUMENT SET\\n\"}]","format":"rich","dateInserted":"2022-05-03T23:50:00+00:00","insertUser":{"userID":147507,"name":"darrink","url":"https:\/\/community.smartsheet.com\/profile\/darrink","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nzDd387pgpY!xsVXscNBPSQ!MYmsYo6wOv0","dateLastActive":"2022-05-04T15:57:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/328219#Comment_328219","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/328219#Comment_328219

    对不起，这是我的错!我想我说得太快了。

    HAS函数中的=号应该有一个逗号。=号是导致错误的原因。顺便说一句，我签名中的链接指向函数列表和帮助页面，以及公式错误消息及其解释列表。这些非常有用。

    =COUNTIFS([引用验证步骤]:[引用验证步骤]，"已批准/已验证"，[合格的交易类型]:[合格的交易类型]，HAS(@cell，“托管服务(建议和引用)”）)

    问候,

    杰夫瑞斯曼，IT业务分析师和项目协调员，三菱电机特灵美国

    链接:Smartsheet功能帮助页面链接:智能表公式错误消息

  • darrink
    darrink ✭
    编辑05/04/22

    谢谢杰夫，是的，这个公式现在起作用了!


    非常感谢!

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

在Smartsheet中创建和编辑公式

交叉表参考的公式组合

Smartsheet功能列表

类别

From your screenshots, it looks like you are referencing the same range - \"Account Profile - Lafayette Square Range 2\" - in both formulas.<\/p>

You need to create a NEW reference, with a different reference name, for the Status Column. <\/p>

=COUNTIF( {Account Profile - Lafayette Square STATUS<\/strong>}, \"Complete\")<\/code><\/p>

To do this, edit your COUNTIF formula, and completely delete \"Account Profile - Lafayette Square Range 2\". Then select Reference Another Sheet, and define a new reference being sure to give it a new name and highlight the proper column.<\/p>

As a best practice, you should give your reference names more meaningful names to help in your troubleshooting.<\/p>

e.g. \"Dollar Values\" and \"Status\" instead of \"Range 2\"<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-15 23:02:36","updateUser":{"userID":118935,"name":"Christina Reid","url":"https:\/\/community.smartsheet.com\/profile\/Christina%20Reid","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-15T23:02:41+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":95396,"type":"question","name":"Incorrect Arguement","excerpt":"Hi all, last ditch effort here to help me keep my sanity. I have a formula that is working in one sheet. I've copied the formula and updated the references (as needed) but the formula on the new sheet returns an #INCORRECT ARGUEMENT error. Original formula (that works) =IFERROR(IF(WEEKDAY(TODAY()) = 1, COUNTIFS({Activity…","categoryID":322,"dateInserted":"2022-09-15T14:38:01+00:00","dateUpdated":"2022-09-15T14:53:13+00:00","dateLastComment":"2022-09-15T17:07:46+00:00","insertUserID":4350,"insertUser":{"userID":4350,"name":"Eric W.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Eric%20W.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!psnB6d7cvTw!wiNAd2boODQ!oitY5juQe8u","dateLastActive":"2022-09-15T17:18:18+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":4350,"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-09-15T17:07:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":33,"score":null,"hot":3326516147,"url":"https:\/\/community.smartsheet.com\/discussion\/95396\/incorrect-arguement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95396\/incorrect-arguement","format":"Rich","lastPost":{"discussionID":95396,"commentID":343690,"name":"Re: Incorrect Arguement","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/343690#Comment_343690","dateInserted":"2022-09-15T17:07: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-09-15T17:07:10+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-09-15T16:17:08+00:00","dateAnswered":"2022-09-15T16:03:19+00:00","acceptedAnswers":[{"commentID":343679,"body":"

Double check your ranges. It looks like the syntax is correct, so the only thing left is that your range shape\/sizes don't match somewhere or two different ranges within the same function are referencing two different sheets.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-15 16:17:08","updateUser":{"userID":4350,"name":"Eric W.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Eric%20W.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!psnB6d7cvTw!wiNAd2boODQ!oitY5juQe8u","dateLastActive":"2022-09-15T17:18: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":[]},{"discussionID":95394,"type":"question","name":"Need formula help.","excerpt":"I have the following formula: =IFERROR(IF([Date Complete]@row = [Due Date]@row, 0, IF([Date Complete]@row > [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) - 1, IF([Date Complete]@row < [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) + 1, \"//www.santa-greenland.com/community/discussion/90737/\"))), \"//www.santa-greenland.com/community/discussion/90737/\") How do I add a contingency for if the…","categoryID":322,"dateInserted":"2022-09-15T14:30:27+00:00","dateUpdated":null,"dateLastComment":"2022-09-15T15:07:10+00:00","insertUserID":152014,"insertUser":{"userID":152014,"name":"Anne BWarren","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Anne%20BWarren","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/YOZTR02C9N57\/nR5KGZ2674DHO.jpg","dateLastActive":"2022-09-15T16:34:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":152014,"lastUser":{"userID":152014,"name":"Anne BWarren","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Anne%20BWarren","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/YOZTR02C9N57\/nR5KGZ2674DHO.jpg","dateLastActive":"2022-09-15T16:34:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3326507857,"url":"https:\/\/community.smartsheet.com\/discussion\/95394\/need-formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95394\/need-formula-help","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":95394,"commentID":343665,"name":"Re: Need formula help.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/343665#Comment_343665","dateInserted":"2022-09-15T15:07:10+00:00","insertUserID":152014,"insertUser":{"userID":152014,"name":"Anne BWarren","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Anne%20BWarren","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/YOZTR02C9N57\/nR5KGZ2674DHO.jpg","dateLastActive":"2022-09-15T16:34:45+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-09-15T15:07:14+00:00","dateAnswered":"2022-09-15T14:52:00+00:00","acceptedAnswers":[{"commentID":343662,"body":"

@Anne BWarren<\/a> <\/p>

You can introduce a whole series of IFs before the one above to handle blank completed date. The portion in bold starts with a logical expression (If Date complete is blank) and specifies a series of nested IFs to consider if that is true, and if the Date Complete is not blank, start working these other nested IFs. (Double check the color coding on the parentheses once you put this in Smartsheet!)<\/p>

=IFERROR(IF([Date Complete]@row = \"//www.santa-greenland.com/community/discussion/90737/\", (IF(TODAY() = [Due Date]@row, 0, IF(TODAY() > [Due Date]@row, NETWORKDAYS([Due Date]@row, TODAY()) - 1, IF(TODAY() < [Due Date]@row, NETWORKDAYS([Due Date]@row, TODAY()) + 1, \"//www.santa-greenland.com/community/discussion/90737/\"))))<\/strong>, IF([Date Complete]@row = [Due Date]@row, 0, IF([Date Complete]@row > [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) - 1, IF([Date Complete]@row < [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) + 1, \"//www.santa-greenland.com/community/discussion/90737/\")))), \"//www.santa-greenland.com/community/discussion/90737/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-15 15:07:14","updateUser":{"userID":152014,"name":"Anne BWarren","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Anne%20BWarren","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/YOZTR02C9N57\/nR5KGZ2674DHO.jpg","dateLastActive":"2022-09-15T16:34: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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">