Want to practice working with formulas directly in Smartsheet?
Check out theFormula Handbook templateto view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.
❓️Product questions Sign in andask it here!The community's got your back.
Wondering if there’s a problem? Check out theStatus Pageand subscribe for notifications.
RGB color combinations?! Sean ","categoryID":322,"dateInserted":"2019-07-04T07:34:04+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-07-05T03:56:03+00:00","insertUserID":59916,"insertUser":{"userID":59916,"name":"sean59916","title":"","url":"https:\/\/community.smartsheet.com\/profile\/sean59916","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-08\/b1\/43\/nb14312faa18c300e84f0ec627a78ccc7.jpg","dateLastActive":"2021-02-20T01:58:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":107,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/52226\/new-formulas-q-hextodec-dectohex","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/52226\/new-formulas-q-hextodec-dectohex","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":52201,"type":"discussion","name":"Copy To Another Sheet","excerpt":"Please help! I was curious if there is a formula that would copy a row to another sheet, only if a certain column meets the criteria. I created two sheets that are exactly the same, but one is named Overtime and the other is Branch Visit. If the Branch Visit column states 'yes' on the Overtime sheet, can that entire row…","categoryID":322,"dateInserted":"2019-07-03T16:23:33+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-07-04T04:09:56+00:00","insertUserID":81926,"insertUser":{"userID":81926,"name":"Tori Peregoy","url":"https:\/\/community.smartsheet.com\/profile\/Tori%20Peregoy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-06\/20\/6d\/n206d2edc6cfadf37620be540eb41c2fc.jpg","dateLastActive":"2019-07-29T15:58:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":92,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/52201\/copy-to-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/52201\/copy-to-another-sheet","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-07\/fc\/b4\/fcb4db6b1780d8f3c3074714048a8d29.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Overtime Ex.jpg"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}],"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}]},{"discussionID":51866,"type":"discussion","name":"Formula to check Child Row checkbox if Parent row contains \"ABC Dept\" (filtering by Department)","excerpt":"I have a \"Master\" project resource sheet with great grandparent rows for \"Current Projects\" and \"Future Projects\", Then grandparent rows for each \"Project\", parent rows for department and child rows for the individual resources per department. I'm trying to create a filter to filter resources by department across…","categoryID":322,"dateInserted":"2019-06-29T07:59:18+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-07-04T03:59:25+00:00","insertUserID":76791,"insertUser":{"userID":76791,"name":"ON","url":"https:\/\/community.smartsheet.com\/profile\/ON","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5VQGPQ3SKF3Q\/nJM16NE3RI2E2.jpg","dateLastActive":"2021-08-09T21:10:44+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":14,"countViews":879,"score":null,"hot":14,"url":"https:\/\/community.smartsheet.com\/discussion\/51866\/formula-to-check-child-row-checkbox-if-parent-row-contains-abc-dept-filtering-by-department","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/51866\/formula-to-check-child-row-checkbox-if-parent-row-contains-abc-dept-filtering-by-department","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/community.smartsheet.com\/sites\/default\/files\/styles\/thumbnail\/hash\/2019-06\/89\/c8\/89c89359cea6c8ff71eab143d23ab68b.png?itok=ZLTkdnyc","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Untitled"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":210,"urlcode":"human-resources","name":"Human Resources"},{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":391,"urlcode":"product-development","name":"Product Development"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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}]},{"discussionID":52216,"type":"discussion","name":"Finding date in a child when other children are not always dates","excerpt":"I have a parent row that I am trying to use to summarise the Children rows. One column is a date called CloseDate. Rows are added every week, that create in effect a weekly record and the CloseDate is set to the word Active until the entry the week that it is closed and then a date is added. The row is not always at the…","categoryID":322,"dateInserted":"2019-07-03T20:24:50+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-07-04T02:42:57+00:00","insertUserID":64021,"insertUser":{"userID":64021,"name":"Frank Falco","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Falco","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/751\/nGNE5C2V439CH.jpeg","dateLastActive":"2023-08-25T04:31:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":74,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/52216\/finding-date-in-a-child-when-other-children-are-not-always-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/52216\/finding-date-in-a-child-when-other-children-are-not-always-dates","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-07\/20\/37\/20373cce27d6ec3ba09d4ddfd28230e4.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Parent-child-dates.PNG"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":51926,"type":"discussion","name":"Average formula with dates","excerpt":"Hi all, In our sheets we track the time we spend on a specific task on a certain day. So in our sheets we have for example: Due Date: 1-07-2019; Duration: 10m We can have multiple tasks for the same day. I am trying to come up with a formula in which I can calculate the average spend over the past 60 days on a client.…","categoryID":322,"dateInserted":"2019-07-01T10:02:54+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-07-03T21:03:28+00:00","insertUserID":94536,"insertUser":{"userID":94536,"name":"Lisanne","url":"https:\/\/community.smartsheet.com\/profile\/Lisanne","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-07-01T09:57:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45236,"lastUser":{"userID":45236,"name":"Alejandra","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Alejandra","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-04\/8a\/fe\/n8afeceb592ad5845f46c7d931defba07.JPG","dateLastActive":"2020-05-15T18:56:51+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":363,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/51926\/average-formula-with-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/51926\/average-formula-with-dates","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":45256,"type":"discussion","name":"Calculating an Average based upon a value in other column","excerpt":"I have a calculation to calculate the number of days between two columns. =IFERROR(NETWORKDAYS([Date Submitted]41, [Go Live Date]41), \" \") It works great to return a blank if the customer has not gone live. Then I have formula to calculate the average of the cells. =AVG([Number of Days to Implement]12:[Number of Days to…","categoryID":322,"dateInserted":"2019-04-08T08:52:12+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-07-03T09:14:01+00:00","insertUserID":84616,"insertUser":{"userID":84616,"name":"Kandee Evans","url":"https:\/\/community.smartsheet.com\/profile\/Kandee%20Evans","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B0jXwame4SQ!ZFFWWTGoafw!o86CWMB00as","dateLastActive":"2023-02-28T18:31:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":1487,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/45256\/calculating-an-average-based-upon-a-value-in-other-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/45256\/calculating-an-average-based-upon-a-value-in-other-column","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":220,"urlcode":"hospitality","name":"Hospitality"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}],"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}]},{"discussionID":48301,"type":"discussion","name":"Average % Complete If another Column is Checked","excerpt":"I am trying to get the % Complete for a project by averaging the Project Phases % Complete(which are parents to tasks). * =AVG([% Complete]:[Project Phase], 1) I used this formula to only calculate the values in the row that has been identified as a Project Phase with a check.* Answer is 8.39 * =AVG(CHILDREN()) * 100 -…","categoryID":322,"dateInserted":"2019-05-15T16:55:50+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-07-03T08:03:29+00:00","insertUserID":78261,"insertUser":{"userID":78261,"name":"gwen.pino78261","url":"https:\/\/community.smartsheet.com\/profile\/gwen.pino78261","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-02\/eb\/d1\/nebd1588961cd62e27bca0a8637c64d90.JPG","dateLastActive":"2022-07-28T15:43:47+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":297,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/48301\/average-complete-if-another-column-is-checked","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/48301\/average-complete-if-another-column-is-checked","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+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-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"