@swightman<\/a> <\/p>
<\/p>
You will be adding the formula in Column A and it would be something like this. =IF([Column D]@row=\"Pass\", \"Complete\", IF([Column D]@row=\"Under Review\", \"In Progress\"))<\/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":107492,"type":"question","name":"Countifs with and","excerpt":"I'm working on a metrics sheet and trying to count all the instances where a project is assigned to a particular person (metric@row) within the current year (parent cell) AND ONLY if the project status is New, In Progress or Approval Pending (Metric21, Metric22, and Metric23 in metrics sheet). My formula before factoring…","snippet":"I'm working on a metrics sheet and trying to count all the instances where a project is assigned to a particular person (metric@row) within the current year (parent cell) AND ONLY…","categoryID":322,"dateInserted":"2023-07-12T12:11:29+00:00","dateUpdated":"2023-07-12T12:11:44+00:00","dateLastComment":"2023-07-12T21:19:36+00:00","insertUserID":163321,"insertUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T19:31:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163321,"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-12T21:24:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":41,"score":null,"hot":3378364265,"url":"https:\/\/community.smartsheet.com\/discussion\/107492\/countifs-with-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107492\/countifs-with-and","format":"Rich","lastPost":{"discussionID":107492,"commentID":384900,"name":"Re: Countifs with and","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384900#Comment_384900","dateInserted":"2023-07-12T21:19: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-12T21:24:21+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-12T19:31:29+00:00","dateAnswered":"2023-07-12T18:47:42+00:00","acceptedAnswers":[{"commentID":384862,"body":"
Try this:<\/p>
=COUNTIFS({Broth Launch - Responsible}, FIND(Metric@row, @cell) >= 1, {Broth Launch - Year}, PARENT(), {Status}, OR(@cell = Metric21, @cell = Metric22, @cell = Metric23)<\/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":[]},{"discussionID":107505,"type":"question","name":"Help with multiple OR criteria to return data from 1 of 4 cells to another cell","excerpt":"I have 4 options (logic added to them) for members to select where they work but need their specific clinic to be added to one main Clinic column and am looking for the best formula to have it automatically update to the column. If the Market equals one of the 4 area clinics then it return the correct clinic name for their…","snippet":"I have 4 options (logic added to them) for members to select where they work but need their specific clinic to be added to one main Clinic column and am looking for the best…","categoryID":322,"dateInserted":"2023-07-12T16:06:53+00:00","dateUpdated":null,"dateLastComment":"2023-07-12T16:50:27+00:00","insertUserID":163388,"insertUser":{"userID":163388,"name":"Dpres","title":"Clinical Manager","url":"https:\/\/community.smartsheet.com\/profile\/Dpres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T16:46:42+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163388,"lastUser":{"userID":163388,"name":"Dpres","title":"Clinical Manager","url":"https:\/\/community.smartsheet.com\/profile\/Dpres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T16:46:42+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":17,"score":null,"hot":3378360440,"url":"https:\/\/community.smartsheet.com\/discussion\/107505\/help-with-multiple-or-criteria-to-return-data-from-1-of-4-cells-to-another-cell","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107505\/help-with-multiple-or-criteria-to-return-data-from-1-of-4-cells-to-another-cell","format":"Rich","lastPost":{"discussionID":107505,"commentID":384834,"name":"Re: Help with multiple OR criteria to return data from 1 of 4 cells to another cell","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/384834#Comment_384834","dateInserted":"2023-07-12T16:50:27+00:00","insertUserID":163388,"insertUser":{"userID":163388,"name":"Dpres","title":"Clinical Manager","url":"https:\/\/community.smartsheet.com\/profile\/Dpres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-12T16:46:42+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-12T17:52:45+00:00","dateAnswered":"2023-07-12T16:19:16+00:00","acceptedAnswers":[{"commentID":384819,"body":"Hi @Dpres<\/a> <\/p>If the staff are only working at an option in 1 column at a time you can do this with a simple JOIN:<\/p>
=JOIN([Austin Clinics]@row:[Rio Grande Clinics]@row)<\/p>
This will combine all of the columns, but as 3 are blank it will just show the 1 they are at.<\/p>
If this isn't the case or I've missed something then let me know and we'll see if we can puzzle an alternative out! 😊<\/span><\/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":[]}">
