Formula for projects completed by month

TPALJA
TPALJA ✭✭
inFormulas and Functions

Im looking for a total # of projects that are completed by Month and year

Tags:
· Share on Facebook Share on Twitter

Best Answer

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    How do you have your sheet set up to track the data currently? Depending on how you have things, you might end up needing to have some helper columns to do what you want to do.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

    · Share on Facebook Share on Twitter
  • TPALJA
    TPALJA ✭✭

    I have date columns as 5/31/23 columns as well as month/year columns 5 2023

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hey@TPALJA

    What about creating a Report? Then you can Filter by if the Projects say "Complete", then Group by the Year column, add a second-level Grouping for the Month column, and use Summarize to COUNT how many rows are in each Group.

    Here's a free webinar that goes through these functions:Redesigned Reports with Grouping and Summary Functions

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter
  • TPALJA
    TPALJA ✭✭

    thanks for the tip on reporting. I cant seem to get it to appear correctly on the dashboard.

    I had created a formula for total # of jobs per month and a formula for those that are completed in separate reports. I was hoping to combined the two formulas, I tried to combine the two formulas but its not quite working for me.


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

    =COUNTIF({Job Status}, "Completed")

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi@TPALJA

    To combine two COUNTIF statements, you'll want to use COUNTIFS, plural.

    Try this:

    =COUNTIFS({Lead Follow Up Range 2 Assigned to}, IFERROR(MONTH(@cell), 0) = 1, {Job Status}, "Completed")

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter

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公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
@SarahI<\/a> Yes you can do that just use CHILDREN([COLUMN NAME])<\/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":106886,"type":"question","name":"Risk and Opportunity Matrix - IF\/AND Statements","excerpt":"Hello, I am trying to utilize a Risk & Opportunity Matrix to assign priority levels to risks and opportunities. I am using this formula to try to do this but I keep getting an #INCORRECT ARGUMENT. Does anyone know why? I even tried a test with only one IF\/AND statement to see if it works and I still get the same message.…","categoryID":322,"dateInserted":"2023-06-26T13:58:31+00:00","dateUpdated":"2023-06-26T14:10:30+00:00","dateLastComment":"2023-06-26T15:19:16+00:00","insertUserID":162759,"insertUser":{"userID":162759,"name":"sophiaashepard","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/sophiaashepard","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T15:30:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162759,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T15:19:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3375581867,"url":"https:\/\/community.smartsheet.com\/discussion\/106886\/risk-and-opportunity-matrix-if-and-statements","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106886\/risk-and-opportunity-matrix-if-and-statements","format":"Rich","lastPost":{"discussionID":106886,"commentID":382299,"name":"Re: Risk and Opportunity Matrix - IF\/AND Statements","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382299#Comment_382299","dateInserted":"2023-06-26T15:19:16+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T15:19:24+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\/AXPIZGR6E5VK\/matrix-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Matrix.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T15:30:19+00:00","dateAnswered":"2023-06-26T15:19:16+00:00","acceptedAnswers":[{"commentID":382299,"body":"

Looks like you are forgetting to close out your AND function(s).<\/p>

=IF(AND(.......), <\/strong>\"Critical\")<\/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":106881,"type":"question","name":"Calculate % complete with Date Range and Today's date","excerpt":"Hello community, I need a formula to return % Complete based on a start - end date range and today's date. So for example: Date Range: 06\/18\/23 - 06\/30\/23 Today's date: 06\/26\/23 As there are 12days in this date range, and today's date is day 8 out of 12, the percentage complete would be 66.6% Thanks in advance!","categoryID":322,"dateInserted":"2023-06-26T11:40:17+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T15:14:49+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T15:15:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T15:19:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":44,"score":null,"hot":3375575706,"url":"https:\/\/community.smartsheet.com\/discussion\/106881\/calculate-complete-with-date-range-and-todays-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106881\/calculate-complete-with-date-range-and-todays-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106881,"commentID":382293,"name":"Re: Calculate % complete with Date Range and Today's date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382293#Comment_382293","dateInserted":"2023-06-26T15:14:49+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T15:19:24+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-26T13:50:37+00:00","dateAnswered":"2023-06-26T13:24:44+00:00","acceptedAnswers":[{"commentID":382268,"body":"

=MAX(MIN((TODAY() - Start@row) \/ (Finish@row - Start@row), 1), 0)<\/p>


<\/p>

This does the percentage:<\/p>

(TODAY() - Start@row) \/ (Finish@row - Start@row)<\/p>


<\/p>

This caps it at 100%:<\/p>

MIN(..............., 1)<\/p>


<\/p>

This keeps it from going negative for future tasks:<\/p>

MAX(..............., 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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"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

  • Needing some help with my current smartsheet project

    So I'm coming across some issues with my workflows and functions with my current sheet, and I'm hoping somebody could help me out because I'm stumped. There are boxes I have set up on children rows that get checked manually to confirm a certain portion of the Main Task is complete. I'm currently in search of a way I can…

  • Risk and Opportunity Matrix - IF/AND Statements

    Hello, I am trying to utilize a Risk & Opportunity Matrix to assign priority levels to risks and opportunities. I am using this formula to try to do this but I keep getting an #INCORRECT ARGUMENT. Does anyone know why? I even tried a test with only one IF/AND statement to see if it works and I still get the same message.…

  • Calculate % complete with Date Range and Today's date

    Hello community, I need a formula to return % Complete based on a start - end date range and today's date. So for example: Date Range: 06/18/23 - 06/30/23 Today's date: 06/26/23 As there are 12days in this date range, and today's date is day 8 out of 12, the percentage complete would be 66.6% Thanks in advance!