使用使用sumifs和日期返回一个总和
我的目标是返回一个部门(在本例中为Wire)中所有“工作时间”的总和，这些时间位于Smartsheet报告的“END”日期范围内。
这个公式返回一个值，但是我手工检查过了，我确定这个值是不正确的。我导出了工作表的过滤视图，并按日期排序，然后对小时进行求和。
下面是我使用的公式:=SUMIFS([Work Hours]:[Work Hours]， Department:Department，“Wire”，Finish:Finish， >=DATE(2020, 8, 1)， Finish:Finish， <=DATE(2020, 8, 31))
最佳答案
-
拉里Bejcar ✭我想感谢路易斯和吉纳维芙的快速反应。另外，我之前问过Smartsheet自己的问题，目前我正在研究他们的解决方案。
我们从中提取总和的列取决于Smartsheet在生产依赖项中使用的持续时间的计算。ob欧宝娱乐app手机下载实际上，当单元格读作“小时”时，它背后的数学是一个8小时的工作日。长话短说，当我将结果乘以8时，我得到了我所期望的总数。
我将独立探索刘易斯的解，就像我探索我对智能表的使用一样。
再次感谢大家。
答案
-
拉里Bejcar ✭
我想感谢路易斯和吉纳维芙的快速反应。另外，我之前问过Smartsheet自己的问题，目前我正在研究他们的解决方案。
我们从中提取总和的列取决于Smartsheet在生产依赖项中使用的持续时间的计算。ob欧宝娱乐app手机下载实际上，当单元格读作“小时”时，它背后的数学是一个8小时的工作日。长话短说，当我将结果乘以8时，我得到了我所期望的总数。
我将独立探索刘易斯的解，就像我探索我对智能表的使用一样。
再次感谢大家。
-
吉纳维芙P。 员工管理你好拉里,
感谢您提供更多的细节，让我们知道你发现了什么!
帮助文章参考资料欧宝体育app官方888
类别
Try this:<\/p>
=IF([Date of Site Survey (Product)]@row <> \"//www.santa-greenland.com/community/discussion/70356/\", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \"no\"))<\/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":106244,"type":"question","name":"Multi-part formula with multi select field using references issue","excerpt":"Hello- I am trying to write a formula that includes sheet references, and two conditions. I need to count the number of students in each category and how many of those students chose a specific availability time from a multi-select drop-down menu. This is what I came up with but doesn't work. =COUNTIFS({Student Type},…","categoryID":322,"dateInserted":"2023-06-08T21:31:20+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T14:55:22+00:00","insertUserID":125320,"insertUser":{"userID":125320,"name":"Kimberly Slavsky","url":"https:\/\/community.smartsheet.com\/profile\/Kimberly%20Slavsky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T14:59:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":125320,"lastUser":{"userID":125320,"name":"Kimberly Slavsky","url":"https:\/\/community.smartsheet.com\/profile\/Kimberly%20Slavsky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T14:59:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3372583602,"url":"https:\/\/community.smartsheet.com\/discussion\/106244\/multi-part-formula-with-multi-select-field-using-references-issue","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106244\/multi-part-formula-with-multi-select-field-using-references-issue","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106244,"commentID":379845,"name":"Re: Multi-part formula with multi select field using references issue","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379845#Comment_379845","dateInserted":"2023-06-09T14:55:22+00:00","insertUserID":125320,"insertUser":{"userID":125320,"name":"Kimberly Slavsky","url":"https:\/\/community.smartsheet.com\/profile\/Kimberly%20Slavsky","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T14:59:34+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-09T14:55:06+00:00","dateAnswered":"2023-06-09T05:19:35+00:00","acceptedAnswers":[{"commentID":379763,"body":"
Hi Kimberly, try this.<\/p>
=COUNTIFS({Student Type}, \"University Student\", {Student Availability}, HAS(@cell, \"Monday AM\"))<\/p>
--Anson<\/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"}]},{"discussionID":106232,"type":"question","name":"Check if project start\/end dates fall within specific time frame.","excerpt":"My team works on projects that typically take 6-12 months. I'd like to create a roll-up sheet that identifies which quarters in which a project was worked on. I'm creating a new row for each project and linking in the start and end dates from each of the projects' sheets. Then I've created a new column for each quarter…","categoryID":322,"dateInserted":"2023-06-08T19:44:47+00:00","dateUpdated":"2023-06-08T22:03:24+00:00","dateLastComment":"2023-06-09T14:05:37+00:00","insertUserID":161563,"insertUser":{"userID":161563,"name":"gunnell","title":"Director, eLearning Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/gunnell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T14:01:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":161563,"lastUserID":161563,"lastUser":{"userID":161563,"name":"gunnell","title":"Director, eLearning Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/gunnell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T14:01:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":34,"score":null,"hot":3372574224,"url":"https:\/\/community.smartsheet.com\/discussion\/106232\/check-if-project-start-end-dates-fall-within-specific-time-frame","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106232\/check-if-project-start-end-dates-fall-within-specific-time-frame","format":"Rich","lastPost":{"discussionID":106232,"commentID":379826,"name":"Re: Check if project start\/end dates fall within specific time frame.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379826#Comment_379826","dateInserted":"2023-06-09T14:05:37+00:00","insertUserID":161563,"insertUser":{"userID":161563,"name":"gunnell","title":"Director, eLearning Initiatives","url":"https:\/\/community.smartsheet.com\/profile\/gunnell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T14:01:37+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-09T14:05:51+00:00","dateAnswered":"2023-06-09T14:05:37+00:00","acceptedAnswers":[{"commentID":379826,"body":"
