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.
\"CLOSED\") Where the Range 6 is the Owner column and Range 2 is the Status Column (Open, Closed, Hold). I am getting responses, but I think it is only counting where the owner…","categoryID":322,"dateInserted":"2019-03-11T15:47:11+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-11T16:58:13+00:00","insertUserID":80966,"insertUser":{"userID":80966,"name":"JFrye","url":"https:\/\/community.smartsheet.com\/profile\/JFrye","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-04-11T14:13:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":52616,"lastUser":{"userID":52616,"name":"Nic Larsen","url":"https:\/\/community.smartsheet.com\/profile\/Nic%20Larsen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-08-25T22:38:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":145,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/43321\/dashboard-formulas-need-to-count-if-a-person-is-included-in-owner-column-and-the-task-is-not-close","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43321\/dashboard-formulas-need-to-count-if-a-person-is-included-in-owner-column-and-the-task-is-not-close","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"},{"tagID":281,"urlcode":"support","name":"Support"}],"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":43166,"type":"discussion","name":"Countifs with reference sheet and current sheet criteria","excerpt":"I am trying to create a count if depending on both columns from my main sheet (utilizing reference sheets) and columns in my current sheet. I tried the following formula but I get an incorrect argument set error. The 2 OPS Tracking Range columns are checkboxes that I want to only count if they are not checked.…","categoryID":322,"dateInserted":"2019-03-07T11:27:04+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-11T16:24:04+00:00","insertUserID":76091,"insertUser":{"userID":76091,"name":"Heather K","url":"https:\/\/community.smartsheet.com\/profile\/Heather%20K","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z1PXM7mzwTw!coNWRwWq-3o!g74SgqjzzB1","dateLastActive":"2021-04-22T23:01:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":76091,"lastUser":{"userID":76091,"name":"Heather K","url":"https:\/\/community.smartsheet.com\/profile\/Heather%20K","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!z1PXM7mzwTw!coNWRwWq-3o!g74SgqjzzB1","dateLastActive":"2021-04-22T23:01:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":164,"score":null,"hot":10,"url":"https:\/\/community.smartsheet.com\/discussion\/43166\/countifs-with-reference-sheet-and-current-sheet-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43166\/countifs-with-reference-sheet-and-current-sheet-criteria","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":43041,"type":"discussion","name":"Determine # days from 1 date range to today","excerpt":"Looking to assign a % complete to a project based on days from open to estimated close. Have a column showing total network days from these dates and now want to either nest a function to subtract the overall days from the current days (open to estimated close minus open to today). I am new to Smartsheet so am having a…","categoryID":322,"dateInserted":"2019-03-06T09:22:07+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-11T16:16:57+00:00","insertUserID":80966,"insertUser":{"userID":80966,"name":"JFrye","url":"https:\/\/community.smartsheet.com\/profile\/JFrye","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-04-11T14:13:28+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":5,"countViews":254,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/43041\/determine-days-from-1-date-range-to-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43041\/determine-days-from-1-date-range-to-today","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-03\/ce\/08\/ce081449de91c0c84bdda0165ea17c62.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.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"},{"tagID":281,"urlcode":"support","name":"Support"},{"tagID":324,"urlcode":"community-platform","name":"Community Platform"}],"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":43256,"type":"discussion","name":"Counting Harvey balls by row owner","excerpt":"One column of my sheet is a 5-symbol Harvey ball (Sunny, Partly Sunny, Cloudy, Rainy, Stormy) to show the relative health of a row. Another column is a Contact List type that shows to whom a row is assigned. I would like to extract the total number of non-Sunny rows per team member. What I have done is to add two columns.…","categoryID":322,"dateInserted":"2019-03-09T13:48:32+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-11T14:48:52+00:00","insertUserID":32316,"insertUser":{"userID":32316,"name":"GParkins","url":"https:\/\/community.smartsheet.com\/profile\/GParkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-07-01T13:45:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/749H48MC3045\/nF9FQYPTX0X7D.jpg","dateLastActive":"2023-08-23T14:56:37+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":320,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/43256\/counting-harvey-balls-by-row-owner","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43256\/counting-harvey-balls-by-row-owner","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":211,"urlcode":"manufacturing","name":"Manufacturing"},{"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":42846,"type":"discussion","name":"Linking Sheets","excerpt":"Due to the 200 column limit in a sheet, I have had to split my data into multiple sheets, using the project name as the Primary Column for each sheet. I know how to links cells and use IF formula to pull the Project Name column data, but I cannot for the life of me figure out how to automate it, where if I add a project…","categoryID":322,"dateInserted":"2019-03-04T10:02:28+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-11T13:13:46+00:00","insertUserID":80691,"insertUser":{"userID":80691,"name":"kandacecole","url":"https:\/\/community.smartsheet.com\/profile\/kandacecole","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-03\/8d\/a7\/n8da7cff56cf75d6e992dfd86740206b3.png","dateLastActive":"2019-03-04T09:57:12+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":2,"countViews":81,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/42846\/linking-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/42846\/linking-sheets","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":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":266,"urlcode":"real-estate","name":"Real Estate"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"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":43226,"type":"discussion","name":"Multiple IF statements referencing another sheet","excerpt":"Hello, I'm trying to create a formula that references data in another sheet and looks at multiple criteria from two columns: 1. the name of the person and 2. the entry falls between two dates, then I want to return the value from that sheet. Wondering if I need and IF(AND( formula? Here's what I have so far: =IF({Auditor},…","categoryID":322,"dateInserted":"2019-03-08T10:13:38+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-11T11:46:49+00:00","insertUserID":74146,"insertUser":{"userID":74146,"name":"SummerEdwards","url":"https:\/\/community.smartsheet.com\/profile\/SummerEdwards","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-06-02T20:36:59+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":5,"countViews":947,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/43226\/multiple-if-statements-referencing-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43226\/multiple-if-statements-referencing-another-sheet","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":43151,"type":"discussion","name":"SUMIFS and date","excerpt":"Hi All, I am trying to reproduce this excel formula in smartsheet =SUMIFS(Sheet2!C:C,Sheet2!A:A,\"<=\"&A2,Sheet2!B:B,\">=\"&A2) I am trying to get a result equivalent to Column B in sheet 1 (see attached file) based on criteria in sheet 1 on column A from the data in sheet 2. My formula in smartsheet is =SUMIFS({Sheet2 Column…","categoryID":322,"dateInserted":"2019-03-07T10:29:18+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-11T08:14:35+00:00","insertUserID":81131,"insertUser":{"userID":81131,"name":"Windy","url":"https:\/\/community.smartsheet.com\/profile\/Windy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-03-10T13:14:40+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":3,"countViews":244,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/43151\/sumifs-and-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43151\/sumifs-and-date","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-03\/39\/0c\/390c2dfbf308108bc41703f17a027b9d.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Sheet1.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"}],"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-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"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-26T01:04:51+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":23,"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":"
