忽略特定值的INDEX公式
我想在我的工作表索引列，从另一个工作表带来的值。在这个公式中，我希望它忽略特定的值(??, NA)。有没有办法把它合并到我的公式中，使我只引入日期值而不引入其他值?
答案
-
乔有关的 ✭✭✭✭✭也许可以在索引前添加一个IF/OR公式。
=如果(或({来源——运行日期}=“?”,{来源——运行日期}=“NA”),“,指数公式）
- 乔有关的
帮助文章资源欧宝体育app官方888
类别
=(SUM(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, \"XXX\"), {ActiveRequestStatus}, CONTAINS(\"assign\", @cell))) + SUM(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, \"XXX\"), {ActiveRequestStatus}, CONTAINS(\"ready\", @cell)))) \/ (COUNT(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, \"XXX\"), {ActiveRequestStatus}, CONTAINS(\"assign\", @cell))) + COUNT(COLLECT({Active#DaysOpen}, {ActiveSegment}, HAS(@cell, \"XXX\"), {ActiveRequestStatus}, CONTAINS(\"ready\", @cell))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-11-02 20:59:46","updateUser":{"userID":153311,"name":"meb_0101","url":"https:\/\/community.smartsheet.com\/profile\/meb_0101","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-02T22:12:30+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":97258,"type":"question","name":"IF(COUNTIFS......Help Request","excerpt":"Main Sheet: My Current Data Sheet: *I already have a Formula on the Data Sheet for \"Client\" to look at the Main Sheet and if a Client Name is the same, return the value once. And for \"Deliverable\" formula to return the count value of a deliverable if \"Status\" not equal to 'Waiting on Client' Now I want to add an additional…","categoryID":322,"dateInserted":"2022-11-02T15:44:33+00:00","dateUpdated":null,"dateLastComment":"2022-11-02T17:48:51+00:00","insertUserID":153953,"insertUser":{"userID":153953,"name":"otavilog","url":"https:\/\/community.smartsheet.com\/profile\/otavilog","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-02T21:10:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":153953,"lastUser":{"userID":153953,"name":"otavilog","url":"https:\/\/community.smartsheet.com\/profile\/otavilog","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-02T21:10:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3334816404,"url":"https:\/\/community.smartsheet.com\/discussion\/97258\/if-countifs-help-request","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97258\/if-countifs-help-request","format":"Rich","lastPost":{"discussionID":97258,"commentID":349637,"name":"Re: IF(COUNTIFS......Help Request","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/349637#Comment_349637","dateInserted":"2022-11-02T17:48:51+00:00","insertUserID":153953,"insertUser":{"userID":153953,"name":"otavilog","url":"https:\/\/community.smartsheet.com\/profile\/otavilog","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-02T21:10:23+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\/2BG4LC0PRTTR\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-11-02T17:49:59+00:00","dateAnswered":"2022-11-02T17:19:43+00:00","acceptedAnswers":[{"commentID":349629,"body":"
=IF(COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>\"//www.santa-greenland.com/community/discussion/91567/\",{Status},<>\"Waiting on Client\"<\/strong>) = 0, \"//www.santa-greenland.com/community/discussion/91567/\", COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>\"//www.santa-greenland.com/community/discussion/91567/\",{Status},<>\"Waiting on Client\"<\/strong>))<\/p> This should works. Have you tried it?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-11-02 17:49:59","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":"2022-11-02T20:07:05+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":[]},{"discussionID":97252,"type":"question","name":"Formula for Expiry Date + 36 months","excerpt":"Not real savvy with formulas so apologize in advance if this is pretty basic. I need a formula to calculate an expiration date based on an \"Award Date\" + 36 months. I don't currently have a \"helper\" column to designate 36 months but can add if needed. Also, Award Date and Expiry dates are set as a date column. Thanks for…","categoryID":322,"dateInserted":"2022-11-02T12:54:12+00:00","dateUpdated":null,"dateLastComment":"2022-11-02T13:43:24+00:00","insertUserID":154459,"insertUser":{"userID":154459,"name":"Sherill","url":"https:\/\/community.smartsheet.com\/profile\/Sherill","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-02T13:40:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154459,"lastUser":{"userID":154459,"name":"Sherill","url":"https:\/\/community.smartsheet.com\/profile\/Sherill","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-02T13:40:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3334791456,"url":"https:\/\/community.smartsheet.com\/discussion\/97252\/formula-for-expiry-date-36-months","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97252\/formula-for-expiry-date-36-months","format":"Rich","lastPost":{"discussionID":97252,"commentID":349575,"name":"Re: Formula for Expiry Date + 36 months","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/349575#Comment_349575","dateInserted":"2022-11-02T13:43:24+00:00","insertUserID":154459,"insertUser":{"userID":154459,"name":"Sherill","url":"https:\/\/community.smartsheet.com\/profile\/Sherill","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-02T13:40:31+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\/3BKJ4MA16FWI\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-11-02T14:08:42+00:00","dateAnswered":"2022-11-02T13:09:57+00:00","acceptedAnswers":[{"commentID":349565,"body":"
