Formulas

= 1, [Term length (Years)]@row <= 5, Referral@row = \"External\"), 0.03, IF(AND([Term length (Years)]@row >= 6, Referral@row = \"External\"), 0.03, IF(AND([Term length (Years)]@row >= 1, [Term length (Years)]@row <= 5, Referral@row = \"None\"), 0.07, IF(AND([Term length (Years)]@row >= 6,…","categoryID":322,"dateInserted":"2019-03-12T16:31:53+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-13T14:27:00+00:00","insertUserID":79111,"insertUser":{"userID":79111,"name":"phollingsworth79111","url":"https:\/\/community.smartsheet.com\/profile\/phollingsworth79111","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-03-13T09:03:15+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":1,"countViews":63,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/43411\/if-function-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43411\/if-function-error","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":43456,"type":"discussion","name":"NETDAYS based on IF statement","excerpt":"I'm trying to use a NETDAYS function to determine how many days overdue a payment is, but I only want to display that number of days if a column named 'Status' has the value 'Client payment pending'. The function I'm trying to use is =NETDAYS([Date Sent to AP\/CR]398, TODAY()) but I don't know how to work that into a…","categoryID":322,"dateInserted":"2019-03-13T11:13:54+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-13T14:18:44+00:00","insertUserID":81681,"insertUser":{"userID":81681,"name":"SD@HL","url":"https:\/\/community.smartsheet.com\/profile\/SD%40HL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bBPX272FRz4!BFlXAbGyKbw!5CAD_Q514bi","dateLastActive":"2023-04-25T19:18:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":81681,"lastUser":{"userID":81681,"name":"SD@HL","url":"https:\/\/community.smartsheet.com\/profile\/SD%40HL","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bBPX272FRz4!BFlXAbGyKbw!5CAD_Q514bi","dateLastActive":"2023-04-25T19:18:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":463,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/43456\/netdays-based-on-if-statement","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43456\/netdays-based-on-if-statement","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":274,"urlcode":"legal","name":"Legal"},{"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":43471,"type":"discussion","name":"SUMIFS - Contains value in cell from another sheet","excerpt":"Hi All, I want to create a \"Total Vendor Paid Amount\" column on our \"Main Dashboard\". This column will need a formula that pulls information from another dashboard called \"REO Vendor Invoicing (3)\". I want the formula to sum the total invoices paid, in the \"REO Vendor Invoicing (3)\" dashboard, of jobs that contain a cell…","categoryID":322,"dateInserted":"2019-03-13T12:13:51+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-13T13:46:30+00:00","insertUserID":81701,"insertUser":{"userID":81701,"name":"Jmarquez13232","url":"https:\/\/community.smartsheet.com\/profile\/Jmarquez13232","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-03-13T11:47:33+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":1,"countViews":315,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/43471\/sumifs-contains-value-in-cell-from-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43471\/sumifs-contains-value-in-cell-from-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":218,"urlcode":"construction","name":"Construction"},{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":238,"urlcode":"finance","name":"Finance"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":266,"urlcode":"real-estate","name":"Real Estate"},{"tagID":285,"urlcode":"solution-center","name":"Solution Center"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":549,"urlcode":"account-and-user-management","name":"Account and User 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":43466,"type":"discussion","name":"Countif - Want to count number of times a word appears, multiple then divide by a certain number","excerpt":"I'm creating a pricing sheet and need to use the countif formula. I am counting the number of times \"High\", \"Moderate\", \"Low\" appears in the column and times by 3, 2 or 1 then dividing it by the total count. For the sake of space, I named my column \"G\". Not sure if Smartsheet recognizes the \"+\" in the formula…","categoryID":322,"dateInserted":"2019-03-13T11:56:21+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-13T13:46:05+00:00","insertUserID":81696,"insertUser":{"userID":81696,"name":"rspengle81696","url":"https:\/\/community.smartsheet.com\/profile\/rspengle81696","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-03-13T11:47:06+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":4,"countViews":219,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/43466\/countif-want-to-count-number-of-times-a-word-appears-multiple-then-divide-by-a-certain-number","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43466\/countif-want-to-count-number-of-times-a-word-appears-multiple-then-divide-by-a-certain-number","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"}],"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":22241,"type":"discussion","name":"Newbie Question - Reports Lookup into Sheet (relational database like)","excerpt":"First, I am sure this has been answered, but I could not find it. I have two smartsheets: Projects and PMs. The PMs sheet has basic info about the PM (Emp ID, Name, Email). The project sheet has all the projects we are working in the company with lots of project metrics. One being Margin %. I want to create, then run a…","categoryID":322,"dateInserted":"2018-03-27T11:53:39+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-13T12:20:24+00:00","insertUserID":42626,"insertUser":{"userID":42626,"name":"matt.stockton42626","url":"https:\/\/community.smartsheet.com\/profile\/matt.stockton42626","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-03-27T11:47:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":81326,"lastUser":{"userID":81326,"name":"kagrawa","url":"https:\/\/community.smartsheet.com\/profile\/kagrawa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-01-02T16:58:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":137,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/22241\/newbie-question-reports-lookup-into-sheet-relational-database-like","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/22241\/newbie-question-reports-lookup-into-sheet-relational-database-like","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":247,"urlcode":"contacts","name":"Contacts"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":265,"urlcode":"reports","name":"Reports"}],"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":43006,"type":"discussion","name":"Unparseable Error","excerpt":"I am trying to create a formula that will return different values based on multiple factors, and the information on the sheet is linked in from another sheet my sheet is attached and the formula I am trying to use is below. Any help is greatly appreciated Here is my formula =IF([% Complete]1 = 1, \"complete\",),if(and([%…","categoryID":322,"dateInserted":"2019-03-05T18:03:19+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-13T10:58:44+00:00","insertUserID":11311,"insertUser":{"userID":11311,"name":"Michelle Phillips","url":"https:\/\/community.smartsheet.com\/profile\/Michelle%20Phillips","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WpPMHzkqXfw!XFFPn3CaCfw!UeQ6wlOEHYx","dateLastActive":"2023-03-01T15:25:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":80301,"lastUser":{"userID":80301,"name":"Debbie Sawyer","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Debbie%20Sawyer","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-02\/6f\/d1\/n6fd11e91b71000f2b152e84c8f072631.jpg","dateLastActive":"2023-08-24T14:07:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":259,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/43006\/unparseable-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43006\/unparseable-error","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-03\/46\/ab\/46ab41f8cc881500bec85cce0871cbb9.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"3-5-2019 4-55-34 PM.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":234,"urlcode":"health-care","name":"Healthcare"},{"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":43406,"type":"discussion","name":"Can I have the default value be a function?","excerpt":"I have a form that asks for someone's name. Most of the time, the form is for that person. There are some cases where a person might fill out the form on behalf of someone else. What I'd like to do is have a \"name\" field and a \"on behalf of\" field. If \"on behalf of\" is blank, I'd like it to default to the value of \"name\".…","categoryID":322,"dateInserted":"2019-03-12T15:27:10+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-03-13T10:39:49+00:00","insertUserID":81556,"insertUser":{"userID":81556,"name":"aleonard9","url":"https:\/\/community.smartsheet.com\/profile\/aleonard9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-03-12T15:24:27+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":634,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/43406\/can-i-have-the-default-value-be-a-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/43406\/can-i-have-the-default-value-be-a-function","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":204,"urlcode":"forms","name":"Forms"},{"tagID":211,"urlcode":"manufacturing","name":"Manufacturing"},{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"}],"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}">