Formulas and Functions

0,1)))) The \"[Listing Key]2:[Listing Key]20\" reflect the chosen cells to crunch the formula. Attached is a…","categoryID":322,"dateInserted":"2018-08-10T12:29:16+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-17T15:07:21+00:00","insertUserID":57646,"insertUser":{"userID":57646,"name":"IamRachal","title":"","url":"https:\/\/community.smartsheet.com\/profile\/IamRachal","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DU1X3772K7BJ\/nO684K0YPLU5U.jpg","dateLastActive":"2023-07-26T21:07:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":57646,"lastUser":{"userID":57646,"name":"IamRachal","title":"","url":"https:\/\/community.smartsheet.com\/profile\/IamRachal","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DU1X3772K7BJ\/nO684K0YPLU5U.jpg","dateLastActive":"2023-07-26T21:07:19+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":62,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/30496\/excel-formula-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30496\/excel-formula-error","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-08\/cf\/1d\/cf1d88300581b036be6c357b492c49cd.JPG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.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":266,"urlcode":"real-estate","name":"Real Estate"},{"tagID":279,"urlcode":"marketing","name":"Marketing"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"}],"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":30761,"type":"discussion","name":"Web form populates parent\/child relationship through category","excerpt":"Hello, was hoping to get some help on an issue i am having .. we have an inventory control sheet .. we would like to use a web form(s) to not only take out something of the inventory but to also add something to the inventory. I added a Category column and that is reflected in a dropdown list that matches all of the…","categoryID":322,"dateInserted":"2018-08-16T08:15:26+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-17T08:22:22+00:00","insertUserID":36626,"insertUser":{"userID":36626,"name":"dana y","url":"https:\/\/community.smartsheet.com\/profile\/dana%20y","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-10-25T11:32:10+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":6,"countViews":469,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/30761\/web-form-populates-parent-child-relationship-through-category","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30761\/web-form-populates-parent-child-relationship-through-category","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":215,"urlcode":"templates","name":"Templates"},{"tagID":219,"urlcode":"sheets","name":"Sheets"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":285,"urlcode":"solution-center","name":"Solution Center"},{"tagID":324,"urlcode":"community-platform","name":"Community Platform"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"},{"tagID":507,"urlcode":"enhancement-request","name":"Enhancement Request"}],"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":30721,"type":"discussion","name":"Completed Tasks Past Due Date","excerpt":"I am trying to make a column with stars that is starred when a date is filled into the completion date column past the due date. The only problem with this is that we have a recurring maintenance sheet, and there is a formula for the Due Date column that automatically populates it based on the last completion date. So…","categoryID":322,"dateInserted":"2018-08-15T17:15:41+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-16T11:14:23+00:00","insertUserID":41001,"insertUser":{"userID":41001,"name":"smuhammad41001","url":"https:\/\/community.smartsheet.com\/profile\/smuhammad41001","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wfjYMGlTziQ!bg_fW3xIVn4!_yU1HvlVRfL","dateLastActive":"2020-02-26T16:21:10+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":4374,"lastUser":{"userID":4374,"name":"J. Craig Williams","title":"","url":"https:\/\/community.smartsheet.com\/profile\/J.%20Craig%20Williams","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/74\/da\/n74da5fe4ff9c5b38c13d6409a3c883a3.jpg","dateLastActive":"2023-05-13T18:07:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":164,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/30721\/completed-tasks-past-due-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30721\/completed-tasks-past-due-date","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":30766,"type":"discussion","name":"Formula Help please","excerpt":"i have a column titled # Wins and wound up with a percentage towards my Wins goal. That said i want to use RYG lights based on how close i am to the goal. So here's what i wrote but i know i'm missing something. Any help would be much appreciated. =IF([# Wins]5<=.66,\"Red\",IF(AND([# Wins]5>.66>.9,\"Yellow,IF(AND[#…","categoryID":322,"dateInserted":"2018-08-16T08:22:47+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-16T10:50:21+00:00","insertUserID":55106,"insertUser":{"userID":55106,"name":"aellis55106","url":"https:\/\/community.smartsheet.com\/profile\/aellis55106","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-16T08:00:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":4374,"lastUser":{"userID":4374,"name":"J. Craig Williams","title":"","url":"https:\/\/community.smartsheet.com\/profile\/J.%20Craig%20Williams","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/74\/da\/n74da5fe4ff9c5b38c13d6409a3c883a3.jpg","dateLastActive":"2023-05-13T18:07:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":51,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/30766\/formula-help-please","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30766\/formula-help-please","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":30621,"type":"discussion","name":"Match Two Entries from Two Separate Form Sheets, Assign Status","excerpt":"I have an \"Assignment\" form that creates a system generated number. The crew foreman will use this form for their assignment, then they will populate a \"Production\" form using the system generated number from the \"Assignment\" form to reflect the production done for that assignment. I'd like have a RYG ball on the…","categoryID":322,"dateInserted":"2018-08-14T13:29:27+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-15T14:38:29+00:00","insertUserID":58026,"insertUser":{"userID":58026,"name":"Chris Sams","url":"https:\/\/community.smartsheet.com\/profile\/Chris%20Sams","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-14T12:59:08+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":82,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/30621\/match-two-entries-from-two-separate-form-sheets-assign-status","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30621\/match-two-entries-from-two-separate-form-sheets-assign-status","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":218,"urlcode":"construction","name":"Construction"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"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":30566,"type":"discussion","name":"return list from another sheet","excerpt":"Anyone able to offer some help on the formula below - trying to return a list of information relative to an employee ID number to a column. There will be several matches per ID, e.g. Matt worked item 1.. 2... 3.. and 7. I want to return the information relative to that ID for those entries. Thanks in advance!…","categoryID":322,"dateInserted":"2018-08-13T12:21:37+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-15T12:35:47+00:00","insertUserID":57846,"insertUser":{"userID":57846,"name":"Matt_w_questions","url":"https:\/\/community.smartsheet.com\/profile\/Matt_w_questions","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-13T12:18:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":195,"lastUser":{"userID":195,"name":"Shaine Greenwood","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Shaine%20Greenwood","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/198\/nBSBY6VR571R7.jpg","dateLastActive":"2021-08-30T16:03:52+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":460,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/30566\/return-list-from-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30566\/return-list-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":238,"urlcode":"finance","name":"Finance"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":263,"urlcode":"government","name":"Government"},{"tagID":281,"urlcode":"support","name":"Support"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":391,"urlcode":"product-development","name":"Product Development"},{"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":30611,"type":"discussion","name":"Summing of colored \"conditional\" cells","excerpt":"Hello All, I am trying to count or sum how many colored cells there are within a row. The rows consist of test questions and are set to turn red if the condition is not met (answer is incorrect). Is there a way to sum all of the condition not met cells? There are 25 cells that have conditions in them.","categoryID":322,"dateInserted":"2018-08-14T09:55:11+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-08-15T08:49:20+00:00","insertUserID":57856,"insertUser":{"userID":57856,"name":"jjanusz","url":"https:\/\/community.smartsheet.com\/profile\/jjanusz","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-01-16T17:42:50+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":4374,"lastUser":{"userID":4374,"name":"J. Craig Williams","title":"","url":"https:\/\/community.smartsheet.com\/profile\/J.%20Craig%20Williams","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/74\/da\/n74da5fe4ff9c5b38c13d6409a3c883a3.jpg","dateLastActive":"2023-05-13T18:07:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":610,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/30611\/summing-of-colored-conditional-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/30611\/summing-of-colored-conditional-cells","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":234,"urlcode":"health-care","name":"Healthcare"},{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"},{"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources