Formulas

\"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}]},{"discussionID":36306,"type":"discussion","name":"Capture Timestamps Before They Change","excerpt":"Is there a way to save a date that something occurs? We have rows that get changed constantly, and the status balls change from Green to Red to Yellow to Gray. Any way we can capture the date that a row's status ball changes to Red, even if it later changes to other colors? Thank you!","categoryID":322,"dateInserted":"2018-11-27T04:34:25+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-09T06:25:57+00:00","insertUserID":59656,"insertUser":{"userID":59656,"name":"SYSPK","title":"","url":"https:\/\/community.smartsheet.com\/profile\/SYSPK","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-09\/8a\/a2\/n8aa26d196e6672ed722182cfae07f814.jpg","dateLastActive":"2023-06-14T08:56:39+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":77806,"lastUser":{"userID":77806,"name":"neelesh","url":"https:\/\/community.smartsheet.com\/profile\/neelesh","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-03-01T09:22:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":135,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/36306\/capture-timestamps-before-they-change","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/36306\/capture-timestamps-before-they-change","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}">