Formulas and Functions

=COUNTIFS({Protocol #}, CONTAINS(\"2009\", @cell)<\/strong>, {Amendment}, @cell <> 1<\/strong>)<\/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":107280,"type":"question","name":"Find Average Number if Criteria is Met","excerpt":"I need to determine the AVG number from a list of numbers if the Year and Week Number criteria are met. For example, if the Year is 2025 (Year Column) and the Week Number is 1 (Week Number Column), I need the average number of days in the FO Node Upgrades Duration column. I know that this formula =COUNTIFS({Year}, \"2025\",…","snippet":"I need to determine the AVG number from a list of numbers if the Year and Week Number criteria are met. For example, if the Year is 2025 (Year Column) and the Week Number is 1…","categoryID":322,"dateInserted":"2023-07-06T14:55:10+00:00","dateUpdated":null,"dateLastComment":"2023-07-06T15:31:04+00:00","insertUserID":148855,"insertUser":{"userID":148855,"name":"cabbsman","url":"https:\/\/community.smartsheet.com\/profile\/cabbsman","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!88cq90f5xTs!8sDpd7uL9Ps!kj4X4avpgGN","dateLastActive":"2023-07-06T15:35:02+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-06T15:30:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3377314574,"url":"https:\/\/community.smartsheet.com\/discussion\/107280\/find-average-number-if-criteria-is-met","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107280\/find-average-number-if-criteria-is-met","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107280,"commentID":383951,"name":"Re: Find Average Number if Criteria is Met","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383951#Comment_383951","dateInserted":"2023-07-06T15:31:04+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-06T15:30:36+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\/Y95NSHH892B0\/screenshot-28110-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot (110).png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-06T15:20:04+00:00","dateAnswered":"2023-07-06T15:17:16+00:00","acceptedAnswers":[{"commentID":383940,"body":"

Try this:<\/p>

=AVG(COLLECT({Duration}, {Year}, @cell = 2025, {Week Number}, @cell = 1))<\/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":107278,"type":"question","name":"Can I change the result of a formula to be blank instead of \"#NO MATCH\"?","excerpt":"I have a formula that is working as expected - pulling names when it finds them in another sheet - and when the name is not there, the result is \"#NO MATCH\". I understand the \"#NO MATCH\" response, but I'm wondering if there is a way for the result to show as blank rather than the words \"#NO MATCH\". Below is the formula I…","snippet":"I have a formula that is working as expected - pulling names when it finds them in another sheet - and when the name is not there, the result is \"#NO MATCH\". I understand the \"#NO…","categoryID":322,"dateInserted":"2023-07-06T14:34:47+00:00","dateUpdated":"2023-07-06T14:35:32+00:00","dateLastComment":"2023-07-06T15:03:59+00:00","insertUserID":119189,"insertUser":{"userID":119189,"name":"Judith Campf","url":"https:\/\/community.smartsheet.com\/profile\/Judith%20Campf","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!0ijUf49peDY!!_tJuL4Rs2r1","dateLastActive":"2023-07-06T15:20:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":119189,"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-06T15:30:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3377311726,"url":"https:\/\/community.smartsheet.com\/discussion\/107278\/can-i-change-the-result-of-a-formula-to-be-blank-instead-of-no-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107278\/can-i-change-the-result-of-a-formula-to-be-blank-instead-of-no-match","format":"Rich","lastPost":{"discussionID":107278,"commentID":383934,"name":"Re: Can I change the result of a formula to be blank instead of \"#NO MATCH\"?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383934#Comment_383934","dateInserted":"2023-07-06T15:03:59+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-06T15:30:36+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-07-06T15:20:17+00:00","dateAnswered":"2023-07-06T15:03:59+00:00","acceptedAnswers":[{"commentID":383934,"body":"

You would use the same syntax that I have above. Just copy\/paste your original formula (excluding the initial \"=\" symbol that indicates the start of a formula) into where I have \"original_formula\".<\/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":[]}">