带日期范围的CountIf

lnelsen
lnelsen ✭✭
编辑09/06/22 公式和函数

我试图计算请求类型的数量之间的日期范围。这是我认为可行的方法:

=COUNTIF(({请求跟踪范围4 -请求类型},Category5), AND({请求跟踪范围3 -请求日期},Date (2019,01, 02:2019, 03,14)))

我使用这个公式来计算整个列=COUNTIF(({请求跟踪器范围4 -请求类型},Category5),它工作得很好。当我将日期范围放入其中时,我不再得到结果。

任何建议将不胜感激!

截屏时间:2019-04-08下午3:32.57 .png

标签:

最佳答案

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    ✓回答

    @Kendra P您需要使用AND函数。我还建议引用一个月和一年的数字,而不是两个单独的日期(在我看来,这更容易维护/更新)。


    =COUNTIFS([日期到期]:[日期到期],AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2022))

    thinkspi.com

«13.45

答案

帮助文章参考资料欧宝体育app官方888

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!
It helps me to break out the formula:<\/p>
\n
\n \n \"code1.PNG\"<\/img><\/a>\n <\/div>\n<\/div>\n

You do not need the \"=\" in {Request - Segment}, =\"WellMed\",<\/p>

I'm not sure what you are trying to do with attempting to add \"Accounts Receivable\" to the result of a nested COUNTIF at the end. Is this a mistake?<\/p>"},{"commentID":379471,"body":"

@Carson Penticuff<\/a> <\/p>

=COUNTIFS({Request - Create Date}, IFERROR(MONTH(@cell), 0) = [WSSM#]@row, {Request - Create Date}, IFERROR(YEAR(@cell), 0) = Year@row, {Request - Segment}, \"WellMed\", {WSS}, \"Accounts Receivable\") + COUNTIFS({Complete Create Date}, IFERROR(MONTH(@cell), 0) = [WSSM#]@row, {Complete Create Date}, IFERROR(YEAR(@cell), 0) = Year@row, {Completed Segment}, \"WellMed\", {Completed - WSS}, \"Accounts Receivable\")<\/p>


<\/p>

All Fixed! :)<\/p>


<\/p>

Thanks!<\/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":106160,"type":"question","name":"Can I copy a row to another sheet more than once?","excerpt":"Is it possible to copy a row to another sheet and have it copy multiple times on sheet #2? For example on sheet #1 I have a project with multiple dates for different deliverables within the overall project. I need these projects to copy over to a 2nd sheet where we get a separate line for each date. Right now we are…","categoryID":322,"dateInserted":"2023-06-07T18:30:50+00:00","dateUpdated":null,"dateLastComment":"2023-06-07T18:54:48+00:00","insertUserID":144831,"insertUser":{"userID":144831,"name":"Alywilk","url":"https:\/\/community.smartsheet.com\/profile\/Alywilk","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AlHRh1iFOfw!bRdTGyDgT7Q!6cfIEyX8VjJ","dateLastActive":"2023-06-07T19:16:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":162073,"lastUser":{"userID":162073,"name":"Melissa Yamada","title":"Data Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Melissa%20Yamada","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/JBO7YXU6221D\/nLL8MQQWP4C8M.jpeg","dateLastActive":"2023-06-07T20:51:20+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":28,"score":null,"hot":3372327338,"url":"https:\/\/community.smartsheet.com\/discussion\/106160\/can-i-copy-a-row-to-another-sheet-more-than-once","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106160\/can-i-copy-a-row-to-another-sheet-more-than-once","format":"Rich","tagIDs":[334],"lastPost":{"discussionID":106160,"commentID":379418,"name":"Re: Can I copy a row to another sheet more than once?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379418#Comment_379418","dateInserted":"2023-06-07T18:54:48+00:00","insertUserID":162073,"insertUser":{"userID":162073,"name":"Melissa Yamada","title":"Data Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Melissa%20Yamada","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/JBO7YXU6221D\/nLL8MQQWP4C8M.jpeg","dateLastActive":"2023-06-07T20:51:20+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-07T19:16:21+00:00","dateAnswered":"2023-06-07T18:54:48+00:00","acceptedAnswers":[{"commentID":379418,"body":"

You should be able to do this with the Copy Row workflow automation. Just set a different trigger for the different deliverables so it will get copied more than once<\/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":334,"urlcode":"automations","name":"Automations"}]},{"discussionID":106137,"type":"question","name":"Trying to determine if all children have N\/A Status","excerpt":"I'm working on a formula for summary rows to look at the % Complete of the row as well as the statuses of the child rows. I have the % Complete portion handled: =IF([% Complete]@row = 1, \"Complete\", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), \"In Progress\", IF([% Complete]@row = 0, \"Not Started\"))) I want to add in…","categoryID":322,"dateInserted":"2023-06-07T14:52:30+00:00","dateUpdated":null,"dateLastComment":"2023-06-07T15:16:49+00:00","insertUserID":157541,"insertUser":{"userID":157541,"name":"ro.fei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/ro.fei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4OWA5DDSR8JB\/n2H4UP1N6TZA6.jpg","dateLastActive":"2023-06-07T17:59:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":157541,"lastUser":{"userID":157541,"name":"ro.fei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/ro.fei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4OWA5DDSR8JB\/n2H4UP1N6TZA6.jpg","dateLastActive":"2023-06-07T17:59:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":24,"score":null,"hot":3372301159,"url":"https:\/\/community.smartsheet.com\/discussion\/106137\/trying-to-determine-if-all-children-have-n-a-status","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106137\/trying-to-determine-if-all-children-have-n-a-status","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106137,"commentID":379294,"name":"Re: Trying to determine if all children have N\/A Status","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379294#Comment_379294","dateInserted":"2023-06-07T15:16:49+00:00","insertUserID":157541,"insertUser":{"userID":157541,"name":"ro.fei","title":"","url":"https:\/\/community.smartsheet.com\/profile\/ro.fei","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4OWA5DDSR8JB\/n2H4UP1N6TZA6.jpg","dateLastActive":"2023-06-07T17:59: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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-07T15:16:56+00:00","dateAnswered":"2023-06-07T15:16:49+00:00","acceptedAnswers":[{"commentID":379294,"body":"

I got it! It's long but it works:<\/p>


<\/p>

=IF(AND(CONTAINS(\"N\/A\", CHILDREN(STATUS@row)), CONTAINS(\"Not Started\", CHILDREN(STATUS@row)) = false, CONTAINS(\"In Progress\", CHILDREN(STATUS@row)) = false, CONTAINS(\"Complete\", CHILDREN(STATUS@row)) = false), \"N\/A\", IF([% Complete]@row = 1, \"Complete\", IF(AND([% Complete]@row > 0, [% Complete]@row < 1), \"In Progress\", IF([% Complete]@row = 0, \"Not Started\"))))<\/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":254,"urlcode":"Formulas","name":"Formulas"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势