计数+包含或查找公式帮助

善待动物组织
善待动物组织 ✭✭
在公式和函数

我需要协助，根据两个标准创建一个公式:

计数“SPI引用值”列，当>0时

当

"日期"列包含或查找"一月"

因此，如果我只选择前六行作为示例，SPI Quantity单元格将返回值4。

我尝试了这个公式，但是不起作用=COUNTIFS([SPI引用值]2:[SPI引用值]7，>0,FIND(“January”，Date2:Date7))

感谢善待动物组织

Screenshot.JPG


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

答案

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!

进入赢取免费会议通行证到ENGAGE 2023。

 了解更多
TODAY(),…","categoryID":322,"dateInserted":"2023-06-22T01:59:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-22T03:13:30+00:00","insertUserID":162138,"insertUser":{"userID":162138,"name":"Louis.Smith","url":"https:\/\/community.smartsheet.com\/profile\/Louis.Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T03:41:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-22T05:23:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":32,"score":null,"hot":3374804596,"url":"https:\/\/community.smartsheet.com\/discussion\/106749\/how-to-count-how-many-interviews-in-the-current-week","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106749\/how-to-count-how-many-interviews-in-the-current-week","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106749,"commentID":381670,"name":"Re: How to count how many interviews in the current week?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381670#Comment_381670","dateInserted":"2023-06-22T03:13:30+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-22T05:23:48+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\/TNZRUD6K3A2N\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-22T03:41:21+00:00","dateAnswered":"2023-06-22T03:13:30+00:00","acceptedAnswers":[{"commentID":381670,"body":"

My apologies, I was counting interviews remaining in the current week. This will give you total interviews for the current week.<\/p>

=COUNTIFS([Interview Date]:[Interview Date], @cell <= TODAY(7 - WEEKDAY(TODAY())), [Interview Date]:[Interview Date], @cell > TODAY() - WEEKDAY(TODAY()), Fleet:Fleet, \"A220\")<\/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":106707,"type":"question","name":"How to count how many interviews in the current week?","excerpt":"I am currently trying to track how many interviews are scheduled in the current week per fleet type? The formula I have is counting 7 days from today but isn't including the current day. =COUNTIFS([Interview Date]:[Interview Date], @cell = TODAY(7), [Interview Date]:[Interview Date], @cell > TODAY(), Fleet:Fleet, \"A220\")","categoryID":322,"dateInserted":"2023-06-21T16:14:49+00:00","dateUpdated":null,"dateLastComment":"2023-06-21T20:20:41+00:00","insertUserID":162138,"insertUser":{"userID":162138,"name":"Louis.Smith","url":"https:\/\/community.smartsheet.com\/profile\/Louis.Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T03:41:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":162138,"lastUser":{"userID":162138,"name":"Louis.Smith","url":"https:\/\/community.smartsheet.com\/profile\/Louis.Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T03:41:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":42,"score":null,"hot":3374745330,"url":"https:\/\/community.smartsheet.com\/discussion\/106707\/how-to-count-how-many-interviews-in-the-current-week","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106707\/how-to-count-how-many-interviews-in-the-current-week","format":"Rich","lastPost":{"discussionID":106707,"commentID":381628,"name":"Re: How to count how many interviews in the current week?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381628#Comment_381628","dateInserted":"2023-06-21T20:20:41+00:00","insertUserID":162138,"insertUser":{"userID":162138,"name":"Louis.Smith","url":"https:\/\/community.smartsheet.com\/profile\/Louis.Smith","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-22T03:41: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\/TNZRUD6K3A2N\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-21T20:21:04+00:00","dateAnswered":"2023-06-21T19:39:48+00:00","acceptedAnswers":[{"commentID":381601,"body":"

@Louis.Smith<\/a>,<\/p>

Awesome, glad it worked! Please remember to click the Answer button if you don't mind. <\/p>

Re: \"Is there a way to create a formula for the current?\" - By this do you mean for the current week?<\/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":106700,"type":"question","name":"If Or Formula Help","excerpt":"My formula is working in Excel, but not in Smartsheet. I am still fairly new to smartsheet so any help would be appreciated. Basically its looking at the GLAcct column and find certain values and creating key terms for those values. Ex: If GLAcct = 710111 or 710121, then show Key Salary.","categoryID":322,"dateInserted":"2023-06-21T15:12:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-21T17:02:32+00:00","insertUserID":162062,"insertUser":{"userID":162062,"name":"mragans23","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/mragans23","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T18:21:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162062,"lastUser":{"userID":162062,"name":"mragans23","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/mragans23","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T18:21:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3374728518,"url":"https:\/\/community.smartsheet.com\/discussion\/106700\/if-or-formula-help","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106700\/if-or-formula-help","format":"Rich","lastPost":{"discussionID":106700,"commentID":381545,"name":"Re: If Or Formula Help","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381545#Comment_381545","dateInserted":"2023-06-21T17:02:32+00:00","insertUserID":162062,"insertUser":{"userID":162062,"name":"mragans23","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/mragans23","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-21T18:21:45+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\/WU7DXUC1MGJ2\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-21T18:54:42+00:00","dateAnswered":"2023-06-21T15:29:14+00:00","acceptedAnswers":[{"commentID":381516,"body":"

Hi @mragans23<\/a>,<\/p>

In Smartsheet you'd need to add @row to the GLAcct portion of your the formula. <\/p>

For example, the first section would be:<\/p>

=IF(OR(GLAcct@row = \"710111\", GLAcct@row = \"710121\"), \"Key Salary\")<\/p>

Once you've entered it for one row, you can then right click the cell and \"Convert to Column Formula\" to have it applied to all rows in the column. <\/p>

Other than that, I think everything should be OK once you add the @row in.<\/p>

Hope that helps; any questions etc. then just post! 😊<\/span><\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 如何计算本周面试的次数?

    我目前正在尝试跟踪每个机队类型在本周安排了多少次面试?我的公式是从今天开始计算7天，但我希望能够计算当前的一周。=COUNTIFS([面试日期]:[面试日期]，@cell =今天(7)，[面试日期]:[面试日期]，@cell >今天()，…

  • 如何计算本周面试的次数?

    我目前正在尝试跟踪每个机队类型在本周安排了多少次面试?我的公式是从今天算起的7天，但不包括今天。=COUNTIFS([面试日期]:[面试日期]，@cell =今天(7)，[面试日期]:[面试日期]，@cell >今天()，舰队:舰队，“A220”)

  • 如果或公式帮助

    我的公式在Excel中工作，但在Smartsheet中不行。我仍然是相当新的智能表，所以任何帮助将不胜感激。它查看GLAcct列，找到某些值并为这些值创建关键字。例如:如果GLAcct = 710111或710121，则显示Key Salary。