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.
Formulas and Functions
Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.
WEEKNUMBER(TODAY()), 1) Can you help me?","categoryID":322,"dateInserted":"2020-06-05T19:45:45+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T21:40:54+00:00","insertUserID":122379,"insertUser":{"userID":122379,"name":"Rodrigo Colsa","url":"https:\/\/community.smartsheet.com\/profile\/Rodrigo%20Colsa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_9AaSbs2fQ!mQvB_jrbMb4!wifTnlGu6uJ","dateLastActive":"2021-02-26T22:32:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":122379,"lastUser":{"userID":122379,"name":"Rodrigo Colsa","url":"https:\/\/community.smartsheet.com\/profile\/Rodrigo%20Colsa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!p_9AaSbs2fQ!mQvB_jrbMb4!wifTnlGu6uJ","dateLastActive":"2021-02-26T22:32:58+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":83,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/68932\/checkbox-formulas-with-2-conditions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68932\/checkbox-formulas-with-2-conditions","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-06-05T21:40:32+00:00","dateAnswered":"2020-06-05T21:33:13+00:00","acceptedAnswers":[]}},"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":68933,"type":"question","name":"How can I count the amount of actions in a certain weekday?","excerpt":"Hi all! I'm trying to setup a formula that allows me to count actions (in red) and that also shows me the weekday in which they were made. The columns I'm using for this are 'S' and 'Action Creation Date'. My formula is currently the following: =COUNTIFS(S:S, \"Red\", [Action Creation Date]:[Action Creation Date], [Action…","categoryID":322,"dateInserted":"2020-06-05T20:10:10+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T21:25:31+00:00","insertUserID":119805,"insertUser":{"userID":119805,"name":"Carolina Rivera","url":"https:\/\/community.smartsheet.com\/profile\/Carolina%20Rivera","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ROVCSRAPWM7G\/nPXLHM4D4BFMJ.jpg","dateLastActive":"2020-09-29T12:26:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":121887,"lastUser":{"userID":121887,"name":"Werner Gerstacker","url":"https:\/\/community.smartsheet.com\/profile\/Werner%20Gerstacker","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GhyEvFDTdJbWSU-3MldZtUntUWWhxx41cCi2SvV","dateLastActive":"2020-11-16T19:25:52+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\/68933\/how-can-i-count-the-amount-of-actions-in-a-certain-weekday","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68933\/how-can-i-count-the-amount-of-actions-in-a-certain-weekday","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8ENLJ8HA66MP\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"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":265,"urlcode":"Reports","name":"Reports"},{"tagID":281,"urlcode":"Support","name":"Support"},{"tagID":285,"urlcode":"Solution Center","name":"Solution Center"}],"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":68922,"type":"question","name":"How do I account for leap years when adding 1 or 3 years to a date?","excerpt":"I have a database of clients on programs with expiry dates based on qualification date. Some programs expire in 3 years and some expire in 1 year. I was adding either 364 or 1094 to the date. Leap year seems to be throwing a wrench into this. Any ideas? Here is my code. I have a few other conditions in the calculation but…","categoryID":322,"dateInserted":"2020-06-05T16:29:45+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T20:21:06+00:00","insertUserID":87721,"insertUser":{"userID":87721,"name":"Pam Grant","url":"https:\/\/community.smartsheet.com\/profile\/Pam%20Grant","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-05\/bd\/a1\/nbda1de8963c3d2ed52a567b6ceb18b1b.jpg","dateLastActive":"2022-07-19T19:32:20+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-01-13T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":114,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/68922\/how-do-i-account-for-leap-years-when-adding-1-or-3-years-to-a-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68922\/how-do-i-account-for-leap-years-when-adding-1-or-3-years-to-a-date","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-06-05T19:50:10+00:00","dateAnswered":"2020-06-05T18:45:15+00:00","acceptedAnswers":[]}},"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":68915,"type":"question","name":"Highlight row after 24 hour period","excerpt":"hello, working on a sheet where product must be \"quarantined\" for a 24 hour period. is it possible for SS to automatically count down after anew row is added then highlight said row after the 24 hour period. it must be 24 hours to the exact second","categoryID":322,"dateInserted":"2020-06-05T13:56:18+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T19:20:44+00:00","insertUserID":122357,"insertUser":{"userID":122357,"name":"Anthony Roberts","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20Roberts","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-06-08T12:29:19+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":16866,"lastUser":{"userID":16866,"name":"L_123","title":"","url":"https:\/\/community.smartsheet.com\/profile\/L_123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-28T22:10:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":54,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/68915\/highlight-row-after-24-hour-period","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68915\/highlight-row-after-24-hour-period","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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":68930,"type":"question","name":"Derive Dates form 3 fields","excerpt":"I have 3 columns I want to use to derive month start and end dates Year Quarter Month of the quarter EXAMPLE: Year = 2020 Quarter = 3 Month of quarter = 2 Start Date= 08\/01\/2020 and End Date = 08\/31\/2020 Maybe use a helper table and VLOOKUP?","categoryID":322,"dateInserted":"2020-06-05T19:10:14+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T19:19:11+00:00","insertUserID":104631,"insertUser":{"userID":104631,"name":"Detrie Zacharias","url":"https:\/\/community.smartsheet.com\/profile\/Detrie%20Zacharias","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nQtXn4Wtj_o!wSnUcJnZ0Dw!lFH98PKZDWF","dateLastActive":"2022-08-03T14:39:58+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-01-13T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":43,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/68930\/derive-dates-form-3-fields","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68930\/derive-dates-form-3-fields","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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":68925,"type":"question","name":"Count in between two dates","excerpt":"Hi guys I am trying to count number of inspection submissions in each month, I tried some of the answers form previous discussions but it did not work for me. can some one help me?","categoryID":322,"dateInserted":"2020-06-05T16:45:48+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T18:50:45+00:00","insertUserID":120335,"insertUser":{"userID":120335,"name":"Lakshman .","url":"https:\/\/community.smartsheet.com\/profile\/Lakshman%20.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-10-30T18:45:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":121887,"lastUser":{"userID":121887,"name":"Werner Gerstacker","url":"https:\/\/community.smartsheet.com\/profile\/Werner%20Gerstacker","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GhyEvFDTdJbWSU-3MldZtUntUWWhxx41cCi2SvV","dateLastActive":"2020-11-16T19:25:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":567,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/68925\/count-in-between-two-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68925\/count-in-between-two-dates","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/69TSU49NZBAE\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-06-05T18:46:05+00:00","dateAnswered":"2020-06-05T18:42:09+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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":68927,"type":"question","name":"Nesting OR into AND?","excerpt":"Hi All, I'm wondering if it is possible to nest an \"OR\" statement into an \"AND\" statement. In short, Task C requires Task A be \"complete\" and Task B be one of two statuses. I'd expect it to work something like this (note, I'm using status names to illustrate the point): =IF(AND(StatusA=\"Complete\", OR(StatusB=\"In Progress\",…","categoryID":322,"dateInserted":"2020-06-05T16:56:56+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T18:39:33+00:00","insertUserID":96636,"insertUser":{"userID":96636,"name":"@emipathy","url":"https:\/\/community.smartsheet.com\/profile\/%40emipathy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-07\/c5\/ef\/nc5eff118fc77eeda68d918f5560078f1.jpg","dateLastActive":"2020-12-09T20:49:41+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-01-13T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":73,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/68927\/nesting-or-into-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68927\/nesting-or-into-and","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"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":68896,"type":"question","name":"% Complete Manually Formula","excerpt":"Smartsheet Community: I need to Manually calculate the % Complete for the Parent Task because I want to use a formula for the children. Does anyone have the formula that the Automatic Project Settings use for % Complete? Thanks","categoryID":322,"dateInserted":"2020-06-04T20:15:07+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T16:05:29+00:00","insertUserID":111176,"insertUser":{"userID":111176,"name":"Anthony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20Barthelemy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-01-24T21:26:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":111176,"lastUser":{"userID":111176,"name":"Anthony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20Barthelemy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-01-24T21:26:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":73,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/68896\/complete-manually-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68896\/complete-manually-formula","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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":68460,"type":"question","name":"Vlookup to another sheet and the use of @cell \/ @row","excerpt":"Hi, I'm using a Vlookup formula (=VLOOKUP(true, {ABC Range 3}, 3, false)) to get back information from another sheet (ABC) only when a row is flagged. When dragging the current formula I only manage to get the value from the first row that is flagged. I guess that @row or @cell could help me solving my problem but I didn't…","categoryID":322,"dateInserted":"2020-05-19T22:33:35+00:00","dateUpdated":"2020-05-19T22:36:19+00:00","dateLastComment":"2020-06-05T15:48:50+00:00","insertUserID":121753,"insertUser":{"userID":121753,"name":"Joh","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Joh","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-06-05T15:50:01+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":121753,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2023-01-14T15:10:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":27,"countViews":755,"score":null,"hot":27,"url":"https:\/\/community.smartsheet.com\/discussion\/68460\/vlookup-to-another-sheet-and-the-use-of-cell-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68460\/vlookup-to-another-sheet-and-the-use-of-cell-row","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-05-20T16:38:01+00:00","dateAnswered":"2020-05-20T16:33:23+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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":68765,"type":"question","name":"Auto-Populate Left Stub for Schedules","excerpt":"Thanks to Paul Newcome, I was able to get a WBS schedule working. Now I need to create several schedules, using the WBS as the left two columns. Here is the problem - I need for all schedules to update whenever a row is added or deleted from the original WBS sheet. Simply using Cell Linking does not achieve this. Here is…","categoryID":322,"dateInserted":"2020-06-01T15:00:29+00:00","dateUpdated":null,"dateLastComment":"2020-06-05T14:49:49+00:00","insertUserID":122023,"insertUser":{"userID":122023,"name":"Trisha Martinez","url":"https:\/\/community.smartsheet.com\/profile\/Trisha%20Martinez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-06-10T21:10:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2023-01-14T15:10:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":65,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/68765\/auto-populate-left-stub-for-schedules","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/68765\/auto-populate-left-stub-for-schedules","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/7DFY8M7QFMYG\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":228,"urlcode":"Project Management","name":"Project Management"},{"tagID":231,"urlcode":"Cell linking","name":"Cell linking"},{"tagID":238,"urlcode":"Finance","name":"Finance"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"},{"tagID":320,"urlcode":"department","name":"department"}],"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}">
Formulas don't always scale very well which is why I suggested a reference table. If you would prefer to work with a formula, then you would use something like this for the example in your original post:<\/p>
=IF(CONTAINS(\"Premium\", [Which newsletter?]@row), IF(CONTAINS(\"Spotlight\", Placement@row), \"1000\", \"900\"), IF(CONTAINS(\"Spotlight\", Placement@row), \"800\", \"700\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-13 20:28:39","updateUser":{"userID":156854,"name":"Alyce R.","url":"https:\/\/community.smartsheet.com\/profile\/Alyce%20R.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T22:37:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":99675,"type":"question","name":"Tracking the cumulative days of a cell value change","excerpt":"I need to create column where i can track a number of days a column is checked. in my sheet, there is a check box field for Active status and another column named Active days. when the active status is checked, then it should record the number of days it is checked in the active days column. when someone unchecks it, the…","categoryID":322,"dateInserted":"2023-01-13T15:52:45+00:00","dateUpdated":"2023-01-13T15:54:51+00:00","dateLastComment":"2023-01-13T21:49:23+00:00","insertUserID":120420,"insertUser":{"userID":120420,"name":"asish lamichhane","url":"https:\/\/community.smartsheet.com\/profile\/asish%20lamichhane","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:26:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":120420,"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-01-13T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":32,"score":null,"hot":3347274728,"url":"https:\/\/community.smartsheet.com\/discussion\/99675\/tracking-the-cumulative-days-of-a-cell-value-change","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99675\/tracking-the-cumulative-days-of-a-cell-value-change","format":"Rich","lastPost":{"discussionID":99675,"commentID":357169,"name":"Re: Tracking the cumulative days of a cell value change","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357169#Comment_357169","dateInserted":"2023-01-13T21:49:23+00:00","insertUserID":45516,"insertUser":{"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-01-13T22:02:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-01-13T20:26:13+00:00","dateAnswered":"2023-01-13T19:17:57+00:00","acceptedAnswers":[{"commentID":357132,"body":"
Sorry about that. I misunderstood the first time I read.<\/p>
<\/p>
You will need to set up the date checked column, and you will also need to set up a second sheet as a recipient for a copy row automation that copies when the box is unchecked. This will capture the static data on the second sheet for how many days the box was checked before it was again unchecked. You will also need a unique identifier on each row (Auto-Number column).<\/p>
Then the formula would be<\/p>
=SUMIFS({Static Sheet Days Checked Column}, {Static Sheet Unique ID Column}, @cell = [Unique ID]@row) + IF([Checkbox Column]@row = 1, TODAY() - [Checked Date]@row, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-13 20:26:11","updateUser":{"userID":120420,"name":"asish lamichhane","url":"https:\/\/community.smartsheet.com\/profile\/asish%20lamichhane","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:26:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"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":99678,"type":"question","name":"Copy a Formula to Multiple Rows, but Keep One Column Ref in the Formula the Same?","excerpt":"Hey all! I understand that when you copy a formula with criteria in certain columns to adjacent columns, it changes the column references to keep the relationship between columns the same (ie, column 10 has a formula that references col 1 & col 3, and copying the formula from col 10 to col 11 would result in the col 11…","categoryID":322,"dateInserted":"2023-01-13T17:40:00+00:00","dateUpdated":"2023-01-13T17:41:04+00:00","dateLastComment":"2023-01-13T17:54:42+00:00","insertUserID":154576,"insertUser":{"userID":154576,"name":"AmandaMerrigan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/AmandaMerrigan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:53:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":154576,"lastUserID":154576,"lastUser":{"userID":154576,"name":"AmandaMerrigan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/AmandaMerrigan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:53:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":13,"score":null,"hot":3347264682,"url":"https:\/\/community.smartsheet.com\/discussion\/99678\/copy-a-formula-to-multiple-rows-but-keep-one-column-ref-in-the-formula-the-same","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99678\/copy-a-formula-to-multiple-rows-but-keep-one-column-ref-in-the-formula-the-same","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":99678,"commentID":357101,"name":"Re: Copy a Formula to Multiple Rows, but Keep One Column Ref in the Formula the Same?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357101#Comment_357101","dateInserted":"2023-01-13T17:54:42+00:00","insertUserID":154576,"insertUser":{"userID":154576,"name":"AmandaMerrigan","title":"","url":"https:\/\/community.smartsheet.com\/profile\/AmandaMerrigan","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-13T20:53:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-01-13T17:54:49+00:00","dateAnswered":"2023-01-13T17:54:42+00:00","acceptedAnswers":[{"commentID":357101,"body":"
Looks like I just needed to reference the help center! I looked here but missed this one. For anyone else looking, just add a \"$\" to keep the column reference an absolute value.<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/3FESRCR5XE46\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
