How to get average # Workdays by Contract Type
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
Best Answers
-
Carson Penticuff ✭✭✭✭✭✭=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 ✭✭✭✭✭✭
Awesome! I'm glad it's working for you.️
Answers
-
Carson Penticuff ✭✭✭✭✭✭
=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.
-
Joan156 ✭✭
Sadly I'm receiving Invalid Data Type message.
-
Carson Penticuff ✭✭✭✭✭✭
Is the [Today's Date] column formatted as a date?
-
Joan156 ✭✭
Yes, it is formatted as a date. Do I need to change it?
-
Carson Penticuff ✭✭✭✭✭✭
No, for the YEAR() and MONTH() functions to work, the format must be a date, so this is correct. The other option is that the entries in the [# Workdays from Receipt to Fully Executed] are text instead of integers.
Here is an experiment to try. Use this formula and it should return the number of entries in the [# Workdays from Receipt to Fully Executed]:[# Workdays from Receipt to Fully Executed] column that are formatted as text. The expected result would be 0.
=COUNTIF([# Workdays from Receipt to Fully Executed]:[# Workdays from Receipt to Fully Executed], ISTEXT(@cell))
-
Joan156 ✭✭
Still Invalid Data Type.
Is it possible that since the # Workdays from Receipt to Fully Executed column includes a formula it is impacting the Sheet Summary?
I appreciate your help. I may have to rethink my project.
-
Joan156 ✭✭
Carson:
You were right all along. I had other extraneous data that impacted the original formula.
It works perfect.
Thank you!
-
Carson Penticuff ✭✭✭✭✭✭
Awesome! I'm glad it's working for you.️
Help Article Resources
Categories
HAS() will only return true under three conditions.<\/p>
- The cell is a multicontact cell and contains a match for the contact<\/li>
- The cell is a multiselect dropdown and contains a match<\/li>
- The cell is not a multicontact or multiselect dropdown and contains ONLY and EXACTLY the text you are matching against. <\/li><\/ol>
I am assuming your row in question is a standard Text\/Number cell? If so, you will need to get somewhat creative with some CONTAINS() statements.<\/p>
This will narrow down your matches to only cells that contain a space both before and after TAC. If, however, TAC can appear as the first or last \"word\" in the cell, it would not match in that circumstance as there would only be a space on one side.<\/p>
=IF(CONTAINS(\" TAC \", [Column Name1]@row), 1, 0)<\/p>
<\/p>If TAC can appear as the first or last \"word\" in the cell, this will cover those possibilities as well.<\/p>
=IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\"), 1, 0)<\/p>
<\/p>If it is possible that TAC will be the exact entry of the cell, you will need an additional statement to cover that, as there would not be spaces on either side. This will add that option.<\/p>
=IF(OR(CONTAINS(\" TAC \", [Column Name1]@row), LEFT([Column Name1]@row, 4) = \"TAC \", RIGHT([Column Name1]@row, 4) = \" TAC\", [Column Name1]@row = \"TAC\"), 1, 0)<\/p>
<\/p>If there are other situations you may have in your sheet, i.e., TAC inside parenthesis, or before or after punctuation, etc, you will additional statements to include those options as well.<\/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":109170,"type":"question","name":"COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","excerpt":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another dropdown of project names to select from), and Severity (this column displays a \"High\", \"Medium\", or \"Low\" value based on the calculated score in column…","snippet":"Hi - I am trying to create a COUNTIFS formula using three criteria in the same sheet - Status (I just want to count the Open items), Impacted IT Projects (which is another…","categoryID":322,"dateInserted":"2023-08-18T20:29:26+00:00","dateUpdated":null,"dateLastComment":"2023-08-18T21:13:12+00:00","insertUserID":165367,"insertUser":{"userID":165367,"name":"Shari D","url":"https:\/\/community.smartsheet.com\/profile\/Shari%20D","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-18T21:12:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":109,"score":null,"hot":3384789758,"url":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109170\/countifs-formula-with-3-criteria-2-of-the-criteria-have-formulas","format":"Rich","lastPost":{"discussionID":109170,"commentID":391505,"name":"Re: COUNTIFS formula with 3 criteria (2 of the criteria have formulas)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391505#Comment_391505","dateInserted":"2023-08-18T21:13:12+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-18T21:34:29+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-08-18T21:12:26+00:00","dateAnswered":"2023-08-18T21:01:48+00:00","acceptedAnswers":[{"commentID":391499,"body":"
