多重嵌套帮助!
所以我有6个调查问题，回答选项是“低于预期，达到预期，超过预期”。我需要它告诉我问题1在每个区域有多少低于预期，以此类推每个回答选项和区域。
到目前为止，这个公式非常有效!
=COUNTIFS([交货时效]:[交货时效]，[交货时效]@row, Region:Region，(电子邮件保护)）
但是，它为空白响应单元格填充的数字只是Region计数，即一个区域作为默认值输入了多少次。我试过了:
=COUNTIFS([交货时效]:[交货时效]，[交货时效]@row, Region:Region，(电子邮件保护)， IF(ISBLANK([交货时效]:[交货时效]，“空白”，“非空白”))))
=COUNTIFS([交货时效]:[交货时效]，[交货时效]@row, Region:Region，(电子邮件保护)， IF(ISBLANK([交货时效性]@row)，“空白”，“非空白”))))
然而，它总是返回说不正确的参数集。我该怎么做才能让Smartsheet社区变得更好呢?
答案
-
你真是个天才!谢谢，效果很好!
-
乔恩·马克·H ✭✭✭✭✭
很高兴我能帮上忙!
帮助文章参考资料欧宝体育app官方888
类别
=INDEX([1st Date Column]@row:[Last Date Column]@row, 1, Index@row)<\/p>
<\/p>
You are going to need to update the column names in the formula to match what you are using in your sheet.<\/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":106087,"type":"question","name":"Conditional Formatting Based on 2 Date Columns, with or without Helper Column","excerpt":"Hi! I've scoured the Community posts, and can't find exactly what I need. I am managing a membership list and need to flag when memberships expire before or on a specific date (06\/29\/2023) so I can contact those members. I have one column with their membership expiration dates, a second column with the 06\/29\/2023 deadline,…","categoryID":322,"dateInserted":"2023-06-06T18:29:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-06T19:02:02+00:00","insertUserID":162111,"insertUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162111,"lastUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":45,"score":null,"hot":3372157304,"url":"https:\/\/community.smartsheet.com\/discussion\/106087\/conditional-formatting-based-on-2-date-columns-with-or-without-helper-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106087\/conditional-formatting-based-on-2-date-columns-with-or-without-helper-column","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":106087,"commentID":379120,"name":"Re: Conditional Formatting Based on 2 Date Columns, with or without Helper Column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379120#Comment_379120","dateInserted":"2023-06-06T19:02:02+00:00","insertUserID":162111,"insertUser":{"userID":162111,"name":"Mlichtenstein","title":"Director","url":"https:\/\/community.smartsheet.com\/profile\/Mlichtenstein","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!BtDbwegK_WQ!wlnbvxwzuT4!fyPHolHHR0X","dateLastActive":"2023-06-06T18:55:36+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\/JWT4M72YQK8N\/screenshot-2023-06-06-142808.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-06 142808.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-06T19:00:34+00:00","dateAnswered":"2023-06-06T18:52:14+00:00","acceptedAnswers":[{"commentID":379113,"body":"
@Mlichtenstein<\/a> <\/p> @Eric Law<\/a> - you are speedy 😊<\/span><\/p> Another option would be to us a Sheet Summary field for the Expiration Helper Column - Mtg Date instead of a column in the sheet.<\/p> Flag column formula: =IFERROR(IF([Membership Expiration]@row <= [Expiration Helper Column - Mtg Date]#, 1, 0), \"No Date\")<\/p> Sheet Summary field (Expiration Helper Column - Mtg Date would equal 6\/29\/23<\/p> Hope this helps. <\/p> Thanks -Peggy<\/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":[{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]},{"discussionID":106073,"type":"question","name":"CRM Templates and Formula for tracking company change","excerpt":"Hello all, Two part question: What success have you had using the Smartsheet CRM templates? We have decent adoption with Smartsheet, and no adoption with salesforce, so are looking to do our CRM in Smartsheet and leverage the data we already have (client info, contracts, etc) Second, I am looking for a way (formula,…","categoryID":322,"dateInserted":"2023-06-06T15:59:35+00:00","dateUpdated":"2023-06-06T16:06:41+00:00","dateLastComment":"2023-06-06T20:09:40+00:00","insertUserID":137695,"insertUser":{"userID":137695,"name":"Tim Starkey","url":"https:\/\/community.smartsheet.com\/profile\/Tim%20Starkey","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uolS96T7mzo!84NRtwSFw3o!n5WODiM4TKP","dateLastActive":"2023-06-06T20:09:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":91566,"lastUserID":137695,"lastUser":{"userID":137695,"name":"Tim Starkey","url":"https:\/\/community.smartsheet.com\/profile\/Tim%20Starkey","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uolS96T7mzo!84NRtwSFw3o!n5WODiM4TKP","dateLastActive":"2023-06-06T20:09:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3372151155,"url":"https:\/\/community.smartsheet.com\/discussion\/106073\/crm-templates-and-formula-for-tracking-company-change","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106073\/crm-templates-and-formula-for-tracking-company-change","format":"Rich","lastPost":{"discussionID":106073,"commentID":379148,"name":"Re: CRM Templates and Formula for tracking company change","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379148#Comment_379148","dateInserted":"2023-06-06T20:09:40+00:00","insertUserID":137695,"insertUser":{"userID":137695,"name":"Tim Starkey","url":"https:\/\/community.smartsheet.com\/profile\/Tim%20Starkey","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!uolS96T7mzo!84NRtwSFw3o!n5WODiM4TKP","dateLastActive":"2023-06-06T20:09:55+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-06-06T20:09:52+00:00","dateAnswered":"2023-06-06T17:41:26+00:00","acceptedAnswers":[{"commentID":379092,"body":" Hi @Tim Starkey<\/a> <\/p> I hope you're well and safe!<\/p> I've developed a couple of CRM solutions for clients. The latest one was the most advanced; in short, we have a Company sheet, Contacts sheet, Opportunities sheet, and more. Combined with forms, Data Shuttle and DataMesh.<\/p> So, yes, it's possible to do what you need, but yours may not need to be as advanced.<\/p> You could store the history in a so-called log sheet.<\/p> Make sense?<\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/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"},"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":[]}">
![\"Conditional](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/DD8Q3FFV2RUK\/conditional-formating.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
