按月完成项目的公式

TPALJA
TPALJA ✭
在公式和函数

我正在寻找的项目的总#是由月和年完成

标签:
· 在Facebook上分享 在Twitter上分享

最佳答案

答案

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    你如何设置你的工作表来跟踪当前的数据?根据您的操作方式，您可能最终需要一些帮助列来执行您想要执行的操作。

    乔纳森·桑德斯，CSM

    “改变总是可怕的，因为它是未知的，但面对未知会让我们变得更强大。”

    · 在Facebook上分享 在Twitter上分享
  • TPALJA
    TPALJA ✭

    我有日期列为5/31/23列以及月/年列5 2023

    · 在Facebook上分享 在Twitter上分享
  • 吉纳维芙P。
    吉纳维芙P。 员工管理
    ✓回答

    嘿@TPALJA

    创建报表怎么样?然后，如果项目显示“完成”，则可以按“过滤”，然后按“年”列分组，为“月”列添加第二级分组，并使用“总结”来计算每个组中有多少行。

    这里有一个免费的网络研讨会，介绍了这些功能:重新设计的报告与分组和汇总功能

    欢呼,

    吉纳维芙

    · 在Facebook上分享 在Twitter上分享
  • TPALJA
    TPALJA ✭

    谢谢你的举报技巧。我似乎不能让它正确地出现在仪表板上。

    我创建了一个计算每月总工作量的公式，以及一个计算在单独报告中完成的工作量的公式。我希望把这两个公式结合起来，我试着把这两个公式结合起来，但对我来说不太管用。


    =COUNTIF({Lead Follow Up Range 2 Assigned to}， IFERROR(MONTH(@cell)， 0) = 1)

    =COUNTIF({作业状态}，"已完成")

    · 在Facebook上分享 在Twitter上分享
  • 吉纳维芙P。
    吉纳维芙P。 员工管理

    嗨@TPALJA

    要组合两个COUNTIF语句，您需要使用COUNTIF年代,复数。

    试试这个:

    =条件统计年代({Lead Follow Up Range 2 Assigned to}， IFERROR(MONTH(@cell)， 0) = 1， {Job Status}， "Completed")

    欢呼,

    吉纳维芙

    · 在Facebook上分享 在Twitter上分享

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

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
@CamSME<\/a> you'll use join and collect with children or decedents as the range. This video might help.<\/p>
\n \n https:\/\/youtu.be\/Dzo0UYjxMmI\n <\/a>\n<\/div>


<\/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":106279,"type":"question","name":"How do I create and If Formula pulling from another sheet to designate indicators?","excerpt":"Good Morning All, I am working on a Overall Health for a Project that looks like this: I need to update the lights by using an if statement looking at three different columns of another sheet that are not next to one another on the sheet, example of that sheet below In the first sheet for each Column I need to look at the…","categoryID":322,"dateInserted":"2023-06-09T16:40:18+00:00","dateUpdated":"2023-06-09T16:47:15+00:00","dateLastComment":"2023-06-11T16:46:45+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-11T16:45:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":162256,"lastUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-11T16:45:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":49,"score":null,"hot":3372834423,"url":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106279\/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators","format":"Rich","lastPost":{"discussionID":106279,"commentID":380007,"name":"Re: How do I create and If Formula pulling from another sheet to designate indicators?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380007#Comment_380007","dateInserted":"2023-06-11T16:46:45+00:00","insertUserID":162256,"insertUser":{"userID":162256,"name":"delaurellc","title":"Senior Consultant","url":"https:\/\/community.smartsheet.com\/profile\/delaurellc","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-11T16:45:18+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\/XSK1SHR157VN\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-11T16:45:30+00:00","dateAnswered":"2023-06-11T12:13:51+00:00","acceptedAnswers":[{"commentID":380000,"body":"

If it isn't weekly, then you would use a COUNTIFS in nested IFs.<\/p>

=IF(COUNTIFS({Criteria}, @cell = \"Infrastructure\", {Due Date}, @cell<= TODAY())> 0, \"Red\", IF(.....<\/p>


<\/p>

You would continue that out and adjust the number of days in the TODAY() function to determine your RYG.<\/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":106286,"type":"question","name":"Health Bubble Turn Colors when Status is Updated","excerpt":"Hello, I am new to Smartsheet. I am trying to get when I change my Status to \"Not Started\" I want the Health bubble to automatically turn grey. And when I change my Status to \"In Progress\" the Health bubble will turn yellow, etc. So far I have: IF(Status1 = \"Not Started\", \"Gray\", \" \"). I want it to change color for each…","categoryID":322,"dateInserted":"2023-06-09T19:29:22+00:00","dateUpdated":"2023-06-09T20:02:36+00:00","dateLastComment":"2023-06-09T21:14:52+00:00","insertUserID":162263,"insertUser":{"userID":162263,"name":"clairehunter","url":"https:\/\/community.smartsheet.com\/profile\/clairehunter","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-10T18:54:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162263,"lastUserID":150413,"lastUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":30,"score":null,"hot":3372684854,"url":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106286\/health-bubble-turn-colors-when-status-is-updated","format":"Rich","tagIDs":[254,440],"lastPost":{"discussionID":106286,"commentID":379933,"name":"Re: Health Bubble Turn Colors when Status is Updated","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379933#Comment_379933","dateInserted":"2023-06-09T21:14:52+00:00","insertUserID":150413,"insertUser":{"userID":150413,"name":"Kleerfyre","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kleerfyre","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0FA9VDUULUEH\/n4HMXW6FGST3I.jpg","dateLastActive":"2023-06-09T21:50: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-10T18:54:41+00:00","dateAnswered":"2023-06-09T21:14:52+00:00","acceptedAnswers":[{"commentID":379933,"body":"

You will need a nested IF formula:<\/p>

=IF(Status@row=\"Not Started\", \"Gray\", IF(Status@row=\"In Progress\", \"Yellow\", IF(Status@row=\"Complete\", \"Green\")))<\/p>


<\/p>

Just add more IF Statements like above for what you need. Put all ) at the very end. Then you can turn it into a column formula by right clicking the cell the formula is in and selecting column formula.<\/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"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 遇到了父单元格聚合子任务名称的公式。

    我想捕获单个项目名称，由子任务跟踪，并将它们聚合在父单元格(所有项目名称)中。这样，当最小化时，我就不必展开来查看任务是针对哪些站点。同样对于Dashboard视图，查看哪些站点是被跟踪的父项目的一部分。截图……

  • 我如何创建和如果公式从另一个工作表拉来指定指标?

    大家早上好，我正在处理一个项目的整体运行状况，它看起来像这样:我需要通过使用if语句来更新灯光，该语句查看另一张工作表中三个不同的列，这些列在工作表中并不相邻，下面是该工作表的示例。在第一张工作表中，对于每个列，我需要查看…

  • 当状态更新时，生命泡泡会变颜色

    你好，我是Smartsheet的新手。我试图得到当我改变我的状态为“未开始”，我想要的健康气泡自动变成灰色。当我将自己的状态更改为“进行中”时，生命值气泡将变成黄色，等等。到目前为止，我有:IF(Status1 = "Not Started"， "Gray"， " ")。我想让它改变颜色…