需要滚动参数公式的帮助

薇薇安庄
薇薇安庄 ✭✭✭✭✭✭
编辑12/09/19 在公式和函数

我正在为一个积分系统进行为期6个月的滚动参数计算。

所涉及的简单数据如下:

1)日期栏

2)分数栏(0 ~ 10分)

3)姓名栏

我需要为每个人计算6个月的滚动积分。

我不知道如何计算每个人在6个月内的积分总和。

(当在8月份时，它将是3月至8月的总和，当在9月份时，它将是4月至9月的总和)。

有人能给点建议吗?

标签:

评论

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

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

交叉表引用的公式组合

智能表功能列表

类别

@Timothy<\/a> ,<\/p>

No worries! I have another solution for you which you can apply in sheet summary. Please find the below formula & screenshot<\/p>

=COUNTIFS([Today- Scheduled]:[Today- Scheduled], <=30, Status:Status, \"Completed\", [Job Type]:[Job Type], \"IN\")<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope I answered your question<\/p>"},{"commentID":344420,"body":"

@Timothy<\/a> <\/p>

Your are welcome @Timothy<\/a>. Kindly accept the answer and Vote-up. It will help other Smartsheet user like you😀<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-22 14:40:56","updateUser":{"userID":150910,"name":"Timothy","url":"https:\/\/community.smartsheet.com\/profile\/Timothy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-22T14:40:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":95731,"type":"question","name":"IF Formula with multiple layers","excerpt":"Hi community, I'm looking for help on creating a multi-layered IF formula for the following guidelines. Anyone an expert that can help me build it out? IF amount is > $100K Then this should be 4 IF amount is <$100K - $125K Then this should be 6 IF amount is <$125K - $150K Then this should be 8 IF amount is <$150K - $175K…","categoryID":322,"dateInserted":"2022-09-21T21:22:26+00:00","dateUpdated":null,"dateLastComment":"2022-09-21T22:48:31+00:00","insertUserID":152671,"insertUser":{"userID":152671,"name":"danielle.taylor","url":"https:\/\/community.smartsheet.com\/profile\/danielle.taylor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-21T22:46:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":152671,"lastUser":{"userID":152671,"name":"danielle.taylor","url":"https:\/\/community.smartsheet.com\/profile\/danielle.taylor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-21T22:46:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3327597057,"url":"https:\/\/community.smartsheet.com\/discussion\/95731\/if-formula-with-multiple-layers","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95731\/if-formula-with-multiple-layers","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":95731,"commentID":344362,"name":"Re: IF Formula with multiple layers","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/344362#Comment_344362","dateInserted":"2022-09-21T22:48:31+00:00","insertUserID":152671,"insertUser":{"userID":152671,"name":"danielle.taylor","url":"https:\/\/community.smartsheet.com\/profile\/danielle.taylor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-21T22:46:47+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":"2022-09-21T22:46:44+00:00","dateAnswered":"2022-09-21T22:39:57+00:00","acceptedAnswers":[{"commentID":344359,"body":"

Let's pretend the column with the financial amounts is: \"Employee Bonus\"<\/p>

This is the formula:<\/p>

=IF([Employee Bonus]@row>225000, 16, IF(AND([Employee Bonus]@row>=200000, [Employee Bonus]@row<=225000), 14, IF(AND([Employee Bonus]@row>=175000, [Employee Bonus]@row<=199999), 12, IF(AND([Employee Bonus]@row>=150000, [Employee Bonus]@row<=174999), 10, IF(AND([Employee Bonus]@row>=125000, [Employee Bonus]@row<=149999), 8, IF(AND([Employee Bonus]@row>=100000, [Employee Bonus]@row<=124999), 6, IF([Employee Bonus]@row<100000, 4, \"//www.santa-greenland.com/community/discussion/54331/\")))))))\n\n<\/pre>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-21 22:46:44","updateUser":{"userID":152671,"name":"danielle.taylor","url":"https:\/\/community.smartsheet.com\/profile\/danielle.taylor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-21T22:46:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":95728,"type":"question","name":"Trying to Figure Out a SUMIFS Formula","excerpt":"I am trying to create a formula that add the 'total pieces of media' if the 'type of media' is 'still' and the 'current \/ upcoming' is empty. The formula I am using is below and I keep getting invalid operation. Not sure what I am doing wrong. Thank you for any help I can get. :) =SUMIFS([Total Pieces of Media]:[Total…","categoryID":322,"dateInserted":"2022-09-21T21:00:42+00:00","dateUpdated":null,"dateLastComment":"2022-09-21T21:42:00+00:00","insertUserID":122484,"insertUser":{"userID":122484,"name":"Olivia Doiron","url":"https:\/\/community.smartsheet.com\/profile\/Olivia%20Doiron","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!meFQPnRXLno!HgjXX29LlqY!R2PknPxzi18","dateLastActive":"2022-09-21T21:38:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":122484,"lastUser":{"userID":122484,"name":"Olivia Doiron","url":"https:\/\/community.smartsheet.com\/profile\/Olivia%20Doiron","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!meFQPnRXLno!HgjXX29LlqY!R2PknPxzi18","dateLastActive":"2022-09-21T21:38:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":33,"score":null,"hot":3327591762,"url":"https:\/\/community.smartsheet.com\/discussion\/95728\/trying-to-figure-out-a-sumifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/95728\/trying-to-figure-out-a-sumifs-formula","format":"Rich","lastPost":{"discussionID":95728,"commentID":344341,"name":"Re: Trying to Figure Out a SUMIFS Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/344341#Comment_344341","dateInserted":"2022-09-21T21:42:00+00:00","insertUserID":122484,"insertUser":{"userID":122484,"name":"Olivia Doiron","url":"https:\/\/community.smartsheet.com\/profile\/Olivia%20Doiron","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!meFQPnRXLno!HgjXX29LlqY!R2PknPxzi18","dateLastActive":"2022-09-21T21:38:01+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":"2022-09-21T21:41:20+00:00","dateAnswered":"2022-09-21T21:07:54+00:00","acceptedAnswers":[{"commentID":344330,"body":"
you don't need the equals sign after your type of media range, you need a comma to distinguish the criterion range from the criteria<\/p>
it should look like this<\/p>
=SUMIFS([Total Pieces of Media]:[Total Pieces of Media], [Type of Media]:[Type of Media] ,\"Still\", [Current \/ Upcoming (USC\/NARA)]:[Current \/ Upcoming (USC\/NARA)], ISBLANK(@cell))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-21 21:41:20","updateUser":{"userID":122484,"name":"Olivia Doiron","url":"https:\/\/community.smartsheet.com\/profile\/Olivia%20Doiron","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!!meFQPnRXLno!HgjXX29LlqY!R2PknPxzi18","dateLastActive":"2022-09-21T21:38:01+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">