@Kris Peeters<\/a> <\/p>you should be able to use =Countifs([Al Javor]@row:[Lisa Young]@row,\"1 - High\")<\/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":108307,"type":"question","name":"Formula to Average Performance Score for Various Service Categories","excerpt":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\" selected, I want a running formula that averages all the civil engineer ratings. I have tried using the =averageif() formula, but I continue to…","snippet":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\"…","categoryID":322,"dateInserted":"2023-07-31T15:35:13+00:00","dateUpdated":"2023-07-31T15:48:17+00:00","dateLastComment":"2023-07-31T18:57:50+00:00","insertUserID":164346,"insertUser":{"userID":164346,"name":"ullkay95","url":"https:\/\/community.smartsheet.com\/profile\/ullkay95","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bmtAmxLQVL0!e6DCx07vJ9c!25n9oP55COS","dateLastActive":"2023-07-31T18:59:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":164346,"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-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":34,"score":null,"hot":3381654183,"url":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","format":"Rich","lastPost":{"discussionID":108307,"commentID":388084,"name":"Re: Formula to Average Performance Score for Various Service Categories","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388084#Comment_388084","dateInserted":"2023-07-31T18:57:50+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-31T19:57:04+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\/2HQ91LNQF3GG\/screenshot-2023-07-31-114610.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-07-31 114610.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-31T18:52:12+00:00","dateAnswered":"2023-07-31T18:48:05+00:00","acceptedAnswers":[{"commentID":388078,"body":"
In that case you would use the same syntax but you would reference the column in the sheet using the appropriate column name. <\/p>
[Column name]:[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":108204,"type":"question","name":"I am trying to return where a contract is in the review process, DOJ review may or may not have date","excerpt":"=IF(Started@row > [To OC&P]@row, \"HSD Contracts\", IF([To OC&P]@row > DOJ@row, \"OC&P\", IF(OR(DOJ@row > [To Contractor]@row, \"DOJ\", IF(DOJ@row = 0, IF([To Contractor]@row > [To OC&P]@row, \"Out for Signature\"), IF([To Contractor]@row > [HSD Signed]@row, \"Out for Signature\", \"//www.santa-greenland.com/community/discussion/73814/\"))))))","snippet":"=IF(Started@row > [To OC&P]@row, \"HSD Contracts\", IF([To OC&P]@row > DOJ@row, \"OC&P\", IF(OR(DOJ@row > [To Contractor]@row, \"DOJ\", IF(DOJ@row = 0, IF([To Contractor]@row > [To…","categoryID":322,"dateInserted":"2023-07-27T17:35:54+00:00","dateUpdated":"2023-07-27T17:36:30+00:00","dateLastComment":"2023-08-01T00:09:58+00:00","insertUserID":164200,"insertUser":{"userID":164200,"name":"mjmitchell","title":"DBO","url":"https:\/\/community.smartsheet.com\/profile\/mjmitchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T00:06:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":164200,"lastUser":{"userID":164200,"name":"mjmitchell","title":"DBO","url":"https:\/\/community.smartsheet.com\/profile\/mjmitchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T00:06:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":48,"score":null,"hot":3381330352,"url":"https:\/\/community.smartsheet.com\/discussion\/108204\/i-am-trying-to-return-where-a-contract-is-in-the-review-process-doj-review-may-or-may-not-have-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108204\/i-am-trying-to-return-where-a-contract-is-in-the-review-process-doj-review-may-or-may-not-have-date","format":"Rich","lastPost":{"discussionID":108204,"commentID":388134,"name":"Re: I am trying to return where a contract is in the review process, DOJ review may or may not have date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388134#Comment_388134","dateInserted":"2023-08-01T00:09:58+00:00","insertUserID":164200,"insertUser":{"userID":164200,"name":"mjmitchell","title":"DBO","url":"https:\/\/community.smartsheet.com\/profile\/mjmitchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-01T00:06:31+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-01T00:10:04+00:00","dateAnswered":"2023-08-01T00:09:58+00:00","acceptedAnswers":[{"commentID":388134,"body":"
This took me a few hours to finally figure out and clearly, I have much to learn related to SmartSheet function rules. This is the final formula that worked. Sharing in case others may have a similar need. <\/p>
=IF(AND(ISBLANK(DOJ@row), ISDATE([To Contractor]@row)), \"Out for Signature\", IF(Started@row > [To OC&P]@row, \"HSD Contracts\", IF([To OC&P]@row > DOJ@row, \"OC&P\", IF(DOJ@row > [To Contractor]@row, \"DOJ\", IF([To Contractor]@row > [HSD Signed]@row, \"Out for Signature\", IF(DOJ@row <> [To Contractor]@row, \"Out for Signature\"))))))<\/p>
Once tested, this can be converted to column 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":[]}">
Trending in Formulas and Functions
Count to show the High Priority Items I would like to add a formula that counts the number of 1 - High, 2 - Med, 3 - Low in each row.
Formula to Average Performance Score for Various Service Categories I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has "civil engineer" selected, I want a running formula that averages all the civil engineer ratings. I have tried using the =averageif() formula, but I continue to…
I am trying to return where a contract is in the review process, DOJ review may or may not have date =IF(Started@row > [To OC&P]@row, "HSD Contracts", IF([To OC&P]@row > DOJ@row, "OC&P", IF(OR(DOJ@row > [To Contractor]@row, "DOJ", IF(DOJ@row = 0, IF([To Contractor]@row > [To OC&P]@row, "Out for Signature"), IF([To Contractor]@row > [HSD Signed]@row, "Out for Signature", ""))))))
Nic Larsen ✭✭✭✭✭✭
Debbie Sawyer ✭✭✭✭✭✭
