计数基于两个标准
你好,
这只是下面的一个例子。我试图得到一个公式的“计数”列。我想从“日期”和“组”返回一个数字。如果在同一组中有两个相同的日期,我希望在该单元格中有一个'2'。如果该组只有一个日期,我希望在该单元格中有一个'1'。明白了吗?
蒂雅
最佳答案
-
吉纳维芙P。 员工管理
@row是一个需要以小写输入的函数。试一试:
= countifs([分娩日期]:[分娩日期],[分娩日期]@row, compound:复合的,复合的@row)
干杯!
吉纳维芙
答案
-
杰夫瑞斯曼 ✭✭✭✭✭✭
问候,
杰夫瑞斯曼,IT业务分析师及项目协调员,三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题,请将其标记为接受,以便其他用户稍后可以找到它。谢谢!
-
JJMunoz ✭
谢谢-我试过了,#不可解析#出现了。
-
吉纳维芙P。 员工管理
在Jeff的示例中,列名需要与您的列名相同。你的屏幕截图有文本“日期”和“组”在一个单元格中,所以公式是:
=条件统计(日期:日期,(电子邮件保护)组:集团(电子邮件保护))
但是,如果存放日期的列实际上被称为“开始日期”,那么您需要将其更新为:
=条件统计([开始日期]:[开始日期],[开始日期]组:集团@row(电子邮件保护))
明白了吗?下面是一篇介绍公式中引用列的文章:在公式中创建单元格或列引用
如果这没有帮助,那么查看实际表的屏幕截图会很有用,其中包括列名和识别列类型,但请屏蔽敏感数据。
欢呼,
吉纳维芙
-
杰夫瑞斯曼 ✭✭✭✭✭✭
谢谢你的介入,吉纳维芙。
@JJMunoz它总是有助于提供列名,数据布局等截屏,包括任何公式,你已经尝试和结果的错误消息。有了这些信息,大多数时候其他用户或社区中的真正专家可以很快地为您指出正确的方向。
问候,
杰夫瑞斯曼,IT业务分析师及项目协调员,三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题,请将其标记为接受,以便其他用户稍后可以找到它。谢谢!
-
JJMunoz ✭
@Jeff瑞斯曼@Genevieve P。谢谢你们两位。还有什么问题?
这是一个截图。这是单元格中的当前公式。
= countifs([分娩日期]:[分娩日期],[分娩日期]@row, compound: compound,(电子邮件保护))
-
吉纳维芙P。 员工管理
@row是一个需要以小写输入的函数。试一试:
= countifs([分娩日期]:[分娩日期],[分娩日期]@row, compound:复合的,复合的@row)
干杯!
吉纳维芙
-
JJMunoz ✭
@Genevieve P。就是这样!谢谢你!我是新手,所以我很感谢你的帮助!
-
吉纳维芙P。 员工管理
没问题!在某种程度上,我们都是新手
这就是社区的伟大之处——有很多很棒的智能手机用户喜欢@Jeff瑞斯曼谁可以帮助回答问题!
我还推荐内容大学如果你的计划有访问它,并且免费新员工培训在线研讨会,在这里。
欢呼,
吉纳维芙
-
杰夫瑞斯曼 ✭✭✭✭✭✭
下面我签名中的两个链接指向函数列表(链接到所有的帮助页面)和公式错误信息列表。两者都是使用Smartsheet公式的宝欧宝体育app官方888贵资源,我几乎每天都在使用它们。
在解决方案中心可以找到另一个非常有用的资源。搜索“公式示例”,你会找到一个模板,你可以添加到你的Smartsheet文件夹。此模板包含所有函数的真实示例,超出了您在函数帮助页面上获得的内容。
问候,
杰夫瑞斯曼,IT业务分析师及项目协调员,三菱电机特灵美国
链接:智能表功能帮助页面链接:Smartsheet公式错误信息
如果我的回答帮助解决了您的问题,请将其标记为接受,以便其他用户稍后可以找到它。谢谢!
帮助文章参考资料欧宝体育app官方888
类别
In addition to the IFERROR, it looks like you have the , 0 in the INDEX portion of the formula instead of the MATCH. Try moving it in one:<\/p>
=INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}, 0))<\/strong><\/p>
Cheers,<\/p>
Genevieve<\/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":106550,"type":"question","name":"Invalid Operation","excerpt":"Good morning, I am trying to sum the entries that are greater than 30 but less than 60 days old. =SUMIFS({011-AP Archive -# of Days to Complete}, >30, {011-AP Archive -# of Days to Complete}, <60) I was able to do this same formula with a COUNTIFS but its' not working the same, what am I missing.","categoryID":322,"dateInserted":"2023-06-16T15:00:58+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156010,"lastUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3373857824,"url":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","format":"Rich","lastPost":{"discussionID":106550,"commentID":381027,"name":"Re: Invalid Operation","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381027#Comment_381027","dateInserted":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+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-16T15:22:31+00:00","dateAnswered":"2023-06-16T15:19:44+00:00","acceptedAnswers":[{"commentID":381026,"body":"
Try this:<\/p>
SUMIFS({011-AP Archive -# of Days to Complete}, {011-AP Archive -# of Days to Complete}, > 30, {011-AP Archive -# of Days to Complete}, < 60)<\/p>
With SUMIFS, you have to include the range to SUM separately from the ranges to compare. In your scenario, they are the same, but can be different.<\/p>
SUMIFS( range, criterion_range1, criterion1, [ criterion_range2, criterion2... ])<\/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":106528,"type":"question","name":"Formula Calculation with a zero value denominator","excerpt":"I'm trying to create a formula to calculate the # Hours per remaining week based off the total HOURS remaining. Calculation takes into consideration the calculated remaining # of weeks on a project. Logic should be: Take Remaining hours \/ # of remaining weeks. If remaining weeks is < 1, simply return the amount of…","categoryID":322,"dateInserted":"2023-06-15T20:54:01+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T16:47:02+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161931,"lastUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3373797663,"url":"https:\/\/community.smartsheet.com\/discussion\/106528\/formula-calculation-with-a-zero-value-denominator","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106528\/formula-calculation-with-a-zero-value-denominator","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106528,"commentID":381042,"name":"Re: Formula Calculation with a zero value denominator","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381042#Comment_381042","dateInserted":"2023-06-16T16:47:02+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+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\/09DN1EF6F1P9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-16T16:47:11+00:00","dateAnswered":"2023-06-16T11:42:38+00:00","acceptedAnswers":[{"commentID":380991,"body":"
You can get round this by altering your formula to do what you're actually after:<\/p>
=IF([Column2]9 >= 1, [Column3]@row \/ [Column2]9, MAX([Column3]@row, 0))<\/p>