Formulas and Functions

=IF(OR(Status@row = \"Complete\", Status@row = \"Canceled\", AND(Status@row = \"In Progress\", End@row>= TODAY())), \"Green\", IF(OR(AND(Status@row = \"In Progress\", End@row<= TODAY(-2)), AND(Status@row = \"Not Started\", Start@row<= TODAY(-2))), \"Red\", \"Yellow\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-13 15:39:50","updateUser":{"userID":156761,"name":"Indiemusicboy","url":"https:\/\/community.smartsheet.com\/profile\/Indiemusicboy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-15T00:02: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":99683,"type":"question","name":"Formula with IF, AND, CONTAINS","excerpt":"I need a formula that generates a different price depending on which options have been selected in two different columns. In one column I'm using the word premium to differentiate between the options (whether or not the text contains \"premium\" - because there are other options in the dropdown that do not contain…","categoryID":322,"dateInserted":"2023-01-13T18:13:16+00:00","dateUpdated":null,"dateLastComment":"2023-01-13T21:55:08+00:00","insertUserID":156854,"insertUser":{"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":"✭"},"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":10,"countViews":43,"score":null,"hot":3347286504,"url":"https:\/\/community.smartsheet.com\/discussion\/99683\/formula-with-if-and-contains","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99683\/formula-with-if-and-contains","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":99683,"commentID":357172,"name":"Re: Formula with IF, AND, CONTAINS","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/357172#Comment_357172","dateInserted":"2023-01-13T21:55:08+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:28:39+00:00","dateAnswered":"2023-01-13T19:36:51+00:00","acceptedAnswers":[{"commentID":357139,"body":"

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":33,"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">