# of Completed tasks between 3 and 2 weeks ago?

Allen4480
Allen4480 ✭
edited 04/03/23 inFormulas and Functions

I can get the number of completed tasks last week (7 days) using

=COUNTIFS(Status:Status, "Complete", Finish:Finish, @cell > TODAY(-7))

How do I get the number of tasks completed, say... between 7-14 days in the past?

thank you,

· Share on Facebook Share on Twitter

Best Answer

  • Eric Law
    Eric Law ✭✭✭✭✭
    Answer ✓

    @Allen4480

    You can do it 1 of 2 ways, you can use an AND formula or just have 2 day formulas.

    =COUNTIFS(Status:Status, "Complete", Finish:Finish, @cell > TODAY(-14), Finish:Finish, @cell < TODAY(-7))

    OR

    =COUNTIFS(Status:Status, "Complete", Finish:Finish, AND(@cell > TODAY(-14), @cell < TODAY(-7))

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @Brittaney Pizzato<\/a> - You would match the max date against the date column, and you don't need Collect in this scenario, so it could read as =INDEX({Project Health}, MATCH(MAX({Date}), {Date}, 0))<\/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":112211,"type":"question","name":"Automation Alert Row Info","excerpt":"Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable the information of the row being sent?","snippet":"Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable…","categoryID":322,"dateInserted":"2023-10-26T15:56:15+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T16:07:33+00:00","insertUserID":169126,"insertUser":{"userID":169126,"name":"J.Sanabria","url":"https:\/\/community.smartsheet.com\/profile\/J.Sanabria","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-26T17:52:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":72271,"lastUser":{"userID":72271,"name":"Ella","url":"https:\/\/community.smartsheet.com\/profile\/Ella","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/981\/nUG9B5NDNGI7G.jpg","dateLastActive":"2023-10-26T18:05:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":14,"score":null,"hot":3396672828,"url":"https:\/\/community.smartsheet.com\/discussion\/112211\/automation-alert-row-info","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112211\/automation-alert-row-info","format":"Rich","lastPost":{"discussionID":112211,"commentID":401856,"name":"Re: Automation Alert Row Info","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401856#Comment_401856","dateInserted":"2023-10-26T16:07:33+00:00","insertUserID":72271,"insertUser":{"userID":72271,"name":"Ella","url":"https:\/\/community.smartsheet.com\/profile\/Ella","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/981\/nUG9B5NDNGI7G.jpg","dateLastActive":"2023-10-26T18:05:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T17:52:33+00:00","dateAnswered":"2023-10-26T16:07:33+00:00","acceptedAnswers":[{"commentID":401856,"body":"

@J.Sanabria<\/a> In the notification set up, under Message includes: you would have to select Message only.<\/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":112203,"type":"question","name":"Help w\/ Sumif date is before today","excerpt":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I have now: =SUMIF([Fiscal Month End]1:[Fiscal Month End]12, <=TODAY(), [Actual Capital Investment]1:[Actual Capital Investment]12) And a…","snippet":"Hi, Hitting a snag w\/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I…","categoryID":322,"dateInserted":"2023-10-26T14:19:00+00:00","dateUpdated":null,"dateLastComment":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":130047,"lastUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":15,"score":null,"hot":3396663791,"url":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112203\/help-w-sumif-date-is-before-today","format":"Rich","lastPost":{"discussionID":112203,"commentID":401837,"name":"Re: Help w\/ Sumif date is before today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401837#Comment_401837","dateInserted":"2023-10-26T15:04:11+00:00","insertUserID":130047,"insertUser":{"userID":130047,"name":"Alex Hackford","url":"https:\/\/community.smartsheet.com\/profile\/Alex%20Hackford","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m_jVn4tpYLQ!zh1V8wNL1sQ!ziK_jPs8qoT","dateLastActive":"2023-10-26T16:09:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"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\/JI13A7YQ5L7E\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JI13A7YQ5L7E\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T15:04:18+00:00","dateAnswered":"2023-10-26T14:24:37+00:00","acceptedAnswers":[{"commentID":401817,"body":"

Your formula looks good! But is your \"Fiscal Month End\" formatted as a date column? It looks like a text (should have the leading 0's for the months). Double click on the header to change it to a Date type column and you should be good to go!<\/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":[]}">

Trending in Formulas and Functions

  • How to pull the most recent status symbol from another sheet?

    Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date/symbol, that symbol should be automatically updated…

  • Automation Alert Row Info

    Hi, I'm working in a workflow a I setup an alert. I run it and I notice that below my custom message, is all the information of the row that I modify. Is it possible to disable the information of the row being sent?

  • Help w/ Sumif date is before today

    Hi, Hitting a snag w/ a very basic sumif. I need to sum the total capital investment column if it's corresponding date value falls before or on today's date. Here's the formula I have now: =SUMIF([Fiscal Month End]1:[Fiscal Month End]12, <=TODAY(), [Actual Capital Investment]1:[Actual Capital Investment]12) And a…