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.
❓️Product questions Sign in andask it here!The community's got your back.
Wondering if there’s a problem? Check out theStatus Pageand subscribe for notifications.
1, IF(COUNTIF(Amount:Amount, Amount1) > 1, 1))) I think i am not…","categoryID":322,"dateInserted":"2020-08-26T08:08:01+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T16:23:38+00:00","insertUserID":120206,"insertUser":{"userID":120206,"name":"Rahul Bhandari","url":"https:\/\/community.smartsheet.com\/profile\/Rahul%20Bhandari","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bZlVm_MBVbw!8iFWN_cX-_o!GqmssQaMSdD","dateLastActive":"2021-10-23T14:48: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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":182,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/70864\/duplicate-check-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70864\/duplicate-check-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":70876,"type":"question","name":"VLOOKUP returning #INVALID REF","excerpt":"I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it. =VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false) Below is the excel sheet formula that does work but does not flow over to the smartsheet. =VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE) If you look at the excel…","categoryID":322,"dateInserted":"2020-08-26T16:19:31+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T16:08:09+00:00","insertUserID":122850,"insertUser":{"userID":122850,"name":"Camie Keuck","url":"https:\/\/community.smartsheet.com\/profile\/Camie%20Keuck","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-06T17:29:06+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":14,"countViews":421,"score":null,"hot":14,"url":"https:\/\/community.smartsheet.com\/discussion\/70876\/vlookup-returning-invalid-ref","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70876\/vlookup-returning-invalid-ref","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JUHD7QEN0087\/smartsheet.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"SMARTSHEET.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-26T20:21:33+00:00","dateAnswered":"2020-08-26T19:58:43+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"},{"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":70831,"type":"question","name":"Combining OR Function with INDEX MATCH.","excerpt":"Hi, I tried using Combination of OR Function with INDEX MATCH and phrased it as below =INDEX({Ref Column}, MATCH(OR([Column #]@row, {Column # SheetA}, {Column # SheetB},{ Column # SheetC}), 0)) but it if throwing #INVALID DATA TYPE. whereas =INDEX({Ref Column}, MATCH([[Column #]@row, { Column # SheetA}, 0)) is working…","categoryID":322,"dateInserted":"2020-08-25T05:24:17+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T16:00:07+00:00","insertUserID":122925,"insertUser":{"userID":122925,"name":"Mindfull","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mindfull","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-02T11:26:13+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":155,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/70831\/combining-or-function-with-index-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70831\/combining-or-function-with-index-match","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-09-24T13:52:25+00:00","dateAnswered":"2020-08-27T16:00:07+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":70890,"type":"question","name":"Formula - Adding together certain assigned point values","excerpt":"Hello, I have created the below formula that works up until the bolded part. I am trying to add this to the below formula: If Less than 100 it gets zero points. If between 100 & 499.9 it gets 1 point If between 500 & 999.9 it gets 2 points If greater than 999.9 (or 1,000 or more) it gets 3 points. I am not sure how to add…","categoryID":322,"dateInserted":"2020-08-26T20:07:25+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T15:51:55+00:00","insertUserID":117663,"insertUser":{"userID":117663,"name":"Maria Watters","url":"https:\/\/community.smartsheet.com\/profile\/Maria%20Watters","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZu-WWnlgGduZfI_LUvXlkG2CG4LmGb5egg0OWksQ=s96-c","dateLastActive":"2023-05-01T20:30:47+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-07-14T20:36:06+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\/70890\/formula-adding-together-certain-assigned-point-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70890\/formula-adding-together-certain-assigned-point-values","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-26T21:40:03+00:00","dateAnswered":"2020-08-26T20:20:55+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":70877,"type":"question","name":"IF > TODAY formula not working","excerpt":"I've been trying for hours to get this formula right and I think I've tried every combination. I have a date field. It can either Have a date Be Blank Have TBD Have N\/A If it has a date AND the date greater than today OR if blank OR if it's TBD = not check If N\/A OR date is passed OR it's blank = checked This is what I…","categoryID":322,"dateInserted":"2020-08-26T16:45:42+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T15:17:11+00:00","insertUserID":118735,"insertUser":{"userID":118735,"name":"Dana Grummer","url":"https:\/\/community.smartsheet.com\/profile\/Dana%20Grummer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-wNDtxEwH3o!!MU9ttqvaLav","dateLastActive":"2022-11-17T20: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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":205,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/70877\/if-today-formula-not-working","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70877\/if-today-formula-not-working","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":70895,"type":"question","name":"Parent Health Status of Children Rows Based On If Certain Criteria Is Met","excerpt":"Hello, I have a Gender column with these options: Female, Male, Transgender, Non-binary\/non-conforming, Prefer not to respond I need the parent row to reflect a status of RED ball if there is no 'Female' represented in any of the children. I currently have this formula, but feel I'm missing significant COUNTIF references:…","categoryID":322,"dateInserted":"2020-08-26T21:26:10+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T11:33:11+00:00","insertUserID":108886,"insertUser":{"userID":108886,"name":"cyndi_walker108886","url":"https:\/\/community.smartsheet.com\/profile\/cyndi_walker108886","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-11\/76\/d4\/n76d497ef96ee057f6ff64eac637263b2.jpg","dateLastActive":"2020-08-27T22:52:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":108886,"lastUser":{"userID":108886,"name":"cyndi_walker108886","url":"https:\/\/community.smartsheet.com\/profile\/cyndi_walker108886","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-11\/76\/d4\/n76d497ef96ee057f6ff64eac637263b2.jpg","dateLastActive":"2020-08-27T22:52:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":95,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/70895\/parent-health-status-of-children-rows-based-on-if-certain-criteria-is-met","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70895\/parent-health-status-of-children-rows-based-on-if-certain-criteria-is-met","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":70879,"type":"question","name":"Pulling in Established Data from 1 Sheet into Another Sheet as a Drop Down","excerpt":"Hi All! I have one base sheet of HR job posting with several descriptive column headers, and one of the columns is a Job ID number. Job ID numbers are hand-keyed and unique. I have a second sheet of candidate names and I want to be able to select an active job ID to assign to that candidate from the original base sheet of…","categoryID":322,"dateInserted":"2020-08-26T17:09:05+00:00","dateUpdated":"2020-08-26T17:10:33+00:00","dateLastComment":"2020-08-27T11:22:10+00:00","insertUserID":108886,"insertUser":{"userID":108886,"name":"cyndi_walker108886","url":"https:\/\/community.smartsheet.com\/profile\/cyndi_walker108886","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-11\/76\/d4\/n76d497ef96ee057f6ff64eac637263b2.jpg","dateLastActive":"2020-08-27T22:52:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":108886,"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-07-14T23:41:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":84,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/70879\/pulling-in-established-data-from-1-sheet-into-another-sheet-as-a-drop-down","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70879\/pulling-in-established-data-from-1-sheet-into-another-sheet-as-a-drop-down","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-27T11:23:33+00:00","dateAnswered":"2020-08-27T11:22:10+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":70496,"type":"question","name":"Copy a cell from another sheet with criteria from another column that has multiple same entries","excerpt":"I have two sheet, Test of Project Intake, which has many columns of data. I want to copy the Project Name column when the Lookup Combo has \"Pending-PPRB\". The problem i am having is the Lookup Combo column has many entries of \"Pending-PPRB\". For each of those entries I want to copy the Project Name to the New Projects…","categoryID":322,"dateInserted":"2020-08-10T19:47:13+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T10:35:08+00:00","insertUserID":121203,"insertUser":{"userID":121203,"name":"Susan Hildebrant","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Susan%20Hildebrant","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-09-24T14:01:48+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-07-14T23:41:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":773,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/70496\/copy-a-cell-from-another-sheet-with-criteria-from-another-column-that-has-multiple-same-entries","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70496\/copy-a-cell-from-another-sheet-with-criteria-from-another-column-that-has-multiple-same-entries","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/OFSM5938F9I5\/test-of-project-intake-sheet.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Test of Project Intake sheet.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-27T12:59:50+00:00","dateAnswered":"2020-08-27T10:35:08+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":70838,"type":"question","name":"100% in conditional formatting","excerpt":"Hi, how can I express 'greater than 100%' in conditional formatting? This is my column: And this is the conditional formatting command I'm using: I've tried 0.01, 1, 1.0 and 100, and it just returns everything red. Any help would be very much appreciated.","categoryID":322,"dateInserted":"2020-08-25T13:22:23+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T10:14:39+00:00","insertUserID":102286,"insertUser":{"userID":102286,"name":"LisaB:-)","url":"https:\/\/community.smartsheet.com\/profile\/LisaB%3A-%29","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-09\/d6\/71\/nd67192aa8a486016e6be264cb81e0c20.jpg","dateLastActive":"2022-08-05T14:40:34+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-07-14T23:41:08+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":12,"countViews":75,"score":null,"hot":12,"url":"https:\/\/community.smartsheet.com\/discussion\/70838\/100-in-conditional-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70838\/100-in-conditional-formatting","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/AYET8UOFI9SW\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-26T15:00:37+00:00","dateAnswered":"2020-08-26T14:53:04+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":70849,"type":"question","name":"3 row automation","excerpt":"I am new to SmartSheet, so I'm not sure what it's capable of. I want to have a template of 3 rows [2 indented behind the first] that I can link to a form for adding rooms\/lots to an existing job. Or, instead of creating a template, automate the creation of indented rows upon row creation from a form. I guess this might…","categoryID":322,"dateInserted":"2020-08-25T18:50:25+00:00","dateUpdated":null,"dateLastComment":"2020-08-26T22:32:20+00:00","insertUserID":125500,"insertUser":{"userID":125500,"name":"Jeffrey Camper","url":"https:\/\/community.smartsheet.com\/profile\/Jeffrey%20Camper","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-30T15:14:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":10959,"lastUser":{"userID":10959,"name":"Ben Goldblatt","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Ben%20Goldblatt","photoUrl":"https:\/\/lh3.googleusercontent.com\/-7T1VCkawxAM\/AAAAAAAAAAI\/AAAAAAAAABI\/aGii4NgwZC0\/photo.jpg","dateLastActive":"2023-04-18T18:10:51+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":154,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/70849\/3-row-automation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70849\/3-row-automation","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/34DPW68R9OMB\/temp-pic2-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"temp_pic2.JPG"},"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">
\"//www.santa-greenland.com/community/categories/formulas-and-functions/\", COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell = Agent@row, Date:Date, @cell = Date@row)) > 3), \"Red\", \"//www.santa-greenland.com/community/categories/formulas-and-functions/\") Once the cell worded red, conditional formatting turned the offending cells red with white text…","snippet":"If anyone is looking for something similar here was my answer and worked 100% =IF(AND(Agent@row <> \"//www.santa-greenland.com/community/categories/formulas-and-functions/\", COUNTM(COLLECT([Product Sold]:[Product Sold], Agent:Agent, @cell =…","categoryID":322,"dateInserted":"2023-07-14T18:18:52+00:00","dateUpdated":"2023-07-14T20:01:58+00:00","dateLastComment":"2023-07-14T20:39:19+00:00","insertUserID":163519,"insertUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-07-14T20:34:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163519,"lastUserID":163519,"lastUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-07-14T20:34:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":37,"score":null,"hot":3378731891,"url":"https:\/\/community.smartsheet.com\/discussion\/107635\/duplicate-detection-assitance","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107635\/duplicate-detection-assitance","format":"Rich","lastPost":{"discussionID":107635,"commentID":385431,"name":"Re: duplicate detection assitance","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385431#Comment_385431","dateInserted":"2023-07-14T20:39:19+00:00","insertUserID":163519,"insertUser":{"userID":163519,"name":"Frank Hammond","title":"Mr.","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20Hammond","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nWVKDFTEFOKBN.png","dateLastActive":"2023-07-14T20:34:11+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/AURLV19Y7741\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-14T20:00:15+00:00","dateAnswered":"2023-07-14T19:48:25+00:00","acceptedAnswers":[{"commentID":385406,"body":"
Right. That was to be inserted in place of the COUNTIFS in your existing formula, but I did forget to add the date piece in.<\/p>
Bro! that was it! Thank you, I would not thought to use CountM(Collect for this at all. <\/p>
<\/p>
100% thank you!😁<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":107627,"type":"question","name":"COUNTIFS question, stumped","excerpt":"I'm trying to count how many \"Yes\" is in the column (options are Yes and No). The column name is \"Do you plan to stay overnight May 1st?\" I feel like this is probably simple but I've been stuck on it for 2 weeks. HALP","snippet":"I'm trying to count how many \"Yes\" is in the column (options are Yes and No). The column name is \"Do you plan to stay overnight May 1st?\" I feel like this is probably simple but…","categoryID":322,"dateInserted":"2023-07-14T15:44:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-14T16:13:34+00:00","insertUserID":163501,"insertUser":{"userID":163501,"name":"mwd","url":"https:\/\/community.smartsheet.com\/profile\/mwd","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-14T16:12:38+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":44,"score":null,"hot":3378703096,"url":"https:\/\/community.smartsheet.com\/discussion\/107627\/countifs-question-stumped","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107627\/countifs-question-stumped","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107627,"commentID":385333,"name":"Re: COUNTIFS question, stumped","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385333#Comment_385333","dateInserted":"2023-07-14T16:13:34+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-07-14T20:36:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-14T16:12:44+00:00","dateAnswered":"2023-07-14T15:54:24+00:00","acceptedAnswers":[{"commentID":385325,"body":"
=COUNTIFS([Do you plan to stay overnight May 1st?]:[Do you plan to stay overnight May 1st?], \"Yes\")<\/p>"},{"commentID":385326,"body":"
Sounds like you just need a basic COUNTIFS.<\/p>
=COUNTIFS([Column Name]:[Column name], @cell = \"Yes\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":107619,"type":"question","name":"Automation for contact column notication, once month by active, for only their projects.","excerpt":"I want to notify everyone in my \"Project Leader\" column of only their own projects that are active once a month. An Automation like... Run once a month on this day at this time, If project status is active, Notify Project Leader of all rows assigned to them. There doesn't seem to already be a way to group projects by…","snippet":"I want to notify everyone in my \"Project Leader\" column of only their own projects that are active once a month. An Automation like... Run once a month on this day at this time,…","categoryID":322,"dateInserted":"2023-07-14T14:13:22+00:00","dateUpdated":null,"dateLastComment":"2023-07-14T17:20:10+00:00","insertUserID":151431,"insertUser":{"userID":151431,"name":"Geilisa","url":"https:\/\/community.smartsheet.com\/profile\/Geilisa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZnspmT_we0s!7Ijp-7vLSzs!HbngNM8iINM","dateLastActive":"2023-07-14T17:17:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151431,"lastUser":{"userID":151431,"name":"Geilisa","url":"https:\/\/community.smartsheet.com\/profile\/Geilisa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZnspmT_we0s!7Ijp-7vLSzs!HbngNM8iINM","dateLastActive":"2023-07-14T17:17:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3378700412,"url":"https:\/\/community.smartsheet.com\/discussion\/107619\/automation-for-contact-column-notication-once-month-by-active-for-only-their-projects","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107619\/automation-for-contact-column-notication-once-month-by-active-for-only-their-projects","format":"Rich","lastPost":{"discussionID":107619,"commentID":385346,"name":"Re: Automation for contact column notication, once month by active, for only their projects.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385346#Comment_385346","dateInserted":"2023-07-14T17:20:10+00:00","insertUserID":151431,"insertUser":{"userID":151431,"name":"Geilisa","url":"https:\/\/community.smartsheet.com\/profile\/Geilisa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZnspmT_we0s!7Ijp-7vLSzs!HbngNM8iINM","dateLastActive":"2023-07-14T17:17:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-14T17:17:54+00:00","dateAnswered":"2023-07-14T14:23:39+00:00","acceptedAnswers":[{"commentID":385281,"body":"
You would set your trigger to be date based and then select the option for Custom Recurrence to set up the monthly portion.<\/p>
You would use a condition of the Status column being \"Active\" to apply the filter.<\/p>
Then the recipient for the Alert would be \"Send To Contacts In A Cell\" and you would select the Project Leader column which will only send rows meeting the previous condition to the appropriate Project Leader(s). So if I am leader on rows 1, 3, and 5 (assuming they are all active), I will only be alerted to rows 1, 3, and 5.<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/NZO376RWLNQF\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
