How to get average # Workdays by Contract Type

Joan156
Joan156
edited 06/30/23 inFormulas and Functions

I'm stumped and hoping the community can help.

Within Sheet Summary of my master grid, I am trying to create an average formula that calculates the average # of workdays by contract type within a specific month--in this case, June 2023. This data will be used in a Monthly Dashboard.

For example:

"NDA/CDA (Non-Disclosure Agreement)" should equal 8.25

"SOW (Statement of Work)" should equal 3.333

Screenshot for SmartSheet Community.jpg


Best Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    =AVG(COLLECT([# Workdays from Receipt to Fully Executed]:[# Workdays from Receipt to Fully Executed], [Contract Type]:[Contract Type], "NDA/CDA (Non-Disclosure Agreement)", [Today's Date]:[Today's Date], MONTH(@cell) = 6, [Today's Date]:[Today's Date], YEAR(@cell) = 2023))

    You will just need to substitute in Month/Year/Contract Type as needed.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    Awesome! I'm glad it's working for you.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
@smbrown929<\/a>, when you add quotes around the percentage, you turn it into a word (vs a number). Try this:<\/p>

=IF([Profit Margin]@row < 0.3, \"Red\", IF(AND([Profit Margin]@row = 0.03), \"Yellow\", \"Green\"))<\/p>

Numbers don't need\/shouldn't have quotes around them.<\/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":107182,"type":"question","name":"Help with Projected milestones and billing sheets","excerpt":"I have a project milestones sheets which estimates when each milestone in a project will be completed and contains the billing amount for the projects milestones: I have a separate sheet to track billings for each month, on this second sheet I need the following:","snippet":"I have a project milestones sheets which estimates when each milestone in a project will be completed and contains the billing amount for the projects milestones: I have a…","categoryID":322,"dateInserted":"2023-07-03T16:45:54+00:00","dateUpdated":null,"dateLastComment":"2023-07-03T21:13:17+00:00","insertUserID":151398,"insertUser":{"userID":151398,"name":"CDS","url":"https:\/\/community.smartsheet.com\/profile\/CDS","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2onTfxjIWr8!xZcqscoVM7o!LgSWEU6G5Df","dateLastActive":"2023-07-03T21:12:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151398,"lastUser":{"userID":151398,"name":"CDS","url":"https:\/\/community.smartsheet.com\/profile\/CDS","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2onTfxjIWr8!xZcqscoVM7o!LgSWEU6G5Df","dateLastActive":"2023-07-03T21:12:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":25,"score":null,"hot":3376824551,"url":"https:\/\/community.smartsheet.com\/discussion\/107182\/help-with-projected-milestones-and-billing-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107182\/help-with-projected-milestones-and-billing-sheets","format":"Rich","lastPost":{"discussionID":107182,"commentID":383595,"name":"Re: Help with Projected milestones and billing sheets","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383595#Comment_383595","dateInserted":"2023-07-03T21:13:17+00:00","insertUserID":151398,"insertUser":{"userID":151398,"name":"CDS","url":"https:\/\/community.smartsheet.com\/profile\/CDS","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2onTfxjIWr8!xZcqscoVM7o!LgSWEU6G5Df","dateLastActive":"2023-07-03T21:12:57+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\/FA2AAPPQE86T\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-03T21:12:56+00:00","dateAnswered":"2023-07-03T20:09:37+00:00","acceptedAnswers":[{"commentID":383583,"body":"

In that case you can still use a SUMIFS, but you will need a helper column on your source sheet that brings the customer name down onto every row. <\/p>

=PARENT([Customer Name]@row <\/p>


<\/p>

Then the SUMIFS would look like this:<\/p>

=SUMIFS({Amount}, {Month}, @cell = 1, {Year}, @cell = 2023, {Helper Column}, @cell = [Customer Name]@row)<\/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":107178,"type":"question","name":"IF statement that can pull data from more than one cell if","excerpt":"We are using Smartsheet as a ticketing system for reporting guest issues. I have set up a form for our team to use where they can select the \"primary issue category\", and then based on the issue they choose, it gives them another drop down where they can choose a \"secondary issue\" where they can be more specific about what…","snippet":"We are using Smartsheet as a ticketing system for reporting guest issues. I have set up a form for our team to use where they can select the \"primary issue category\", and then…","categoryID":322,"dateInserted":"2023-07-03T15:00:13+00:00","dateUpdated":null,"dateLastComment":"2023-07-03T16:59:36+00:00","insertUserID":161052,"insertUser":{"userID":161052,"name":"hannahstayo","title":"Hannah","url":"https:\/\/community.smartsheet.com\/profile\/hannahstayo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-03T16:39:03+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-07-03T20:15:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":44,"score":null,"hot":3376803589,"url":"https:\/\/community.smartsheet.com\/discussion\/107178\/if-statement-that-can-pull-data-from-more-than-one-cell-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107178\/if-statement-that-can-pull-data-from-more-than-one-cell-if","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107178,"commentID":383546,"name":"Re: IF statement that can pull data from more than one cell if","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383546#Comment_383546","dateInserted":"2023-07-03T16:59:36+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-07-03T20:15:02+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-07-03T16:35:33+00:00","dateAnswered":"2023-07-03T16:12:33+00:00","acceptedAnswers":[{"commentID":383537,"body":"

Try this:<\/p>

=JOIN(COLLECT([Maintenance issue - helper]@row:[Security issue - helper]@row, [Maintenance issue - helper]@row:[Security issue - helper]@row, @cell <> \"//www.santa-greenland.com/community/discussion/107146/\"), CHAR(10))<\/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