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.

Check what Support you have access to based on your Plan
Overview of Smartsheet support hours and resources

Share your knowledge!
Help others byanswering questions.

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.

Discussion List

= MONTH(TODAY()), DAY([Start Date]1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR([Start Date]1), YEAR(TODAY()) -…","categoryID":322,"dateInserted":"2019-05-03T15:46:36+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-06T10:35:35+00:00","insertUserID":87471,"insertUser":{"userID":87471,"name":"RDCUB","url":"https:\/\/community.smartsheet.com\/profile\/RDCUB","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZTpakUrfdgzEVhx0z5Hgenq_ET7CsG2-NQT_VA=s96-c","dateLastActive":"2023-04-26T22:02:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":87471,"lastUser":{"userID":87471,"name":"RDCUB","url":"https:\/\/community.smartsheet.com\/profile\/RDCUB","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZTpakUrfdgzEVhx0z5Hgenq_ET7CsG2-NQT_VA=s96-c","dateLastActive":"2023-04-26T22:02:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":92,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/47391\/years-in-service-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47391\/years-in-service-formula","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":47436,"type":"discussion","name":"Macro Functionality","excerpt":"Hi, I am using SS to track projects. At the top of my SS I have a master list of tasks with sub tasks. I expand all this out to copy the whole lot and paste them back in at the bottom for my next project. SS does not allow you to simply copy the unexpanded parent row and copy all child rows. My question here is as follows.…","categoryID":322,"dateInserted":"2019-05-06T03:03:56+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-06T05:08:29+00:00","insertUserID":75516,"insertUser":{"userID":75516,"name":"Vasiles Kiosses","url":"https:\/\/community.smartsheet.com\/profile\/Vasiles%20Kiosses","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!88lZd_GLxTw!XafbX32yFD4!fFf4hP8902_","dateLastActive":"2021-02-11T09:52:57+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-17T15:55:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":1753,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/47436\/macro-functionality","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47436\/macro-functionality","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":218,"urlcode":"Construction","name":"Construction"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":271,"urlcode":"Services","name":"Services"},{"tagID":281,"urlcode":"Support","name":"Support"},{"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":47256,"type":"discussion","name":"Nested If Statement - help needed","excerpt":"I'm trying to get this formula to work. =IF([PRO Number]9 - [Trailer #]9 > 10), \" PRO Levels: OK\", IF([PRO Number]9 = [Trailer #]9, \"Yusen Needs PRO#s\"))) I want to trigger the IF\/Then statement if the value of ([PRO Number]9 - [Trailer#]9) is equal to or less than 10. See screen shot below. ","categoryID":322,"dateInserted":"2019-05-02T13:45:41+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-04T12:44:27+00:00","insertUserID":76066,"insertUser":{"userID":76066,"name":"Jed Rusyniak","url":"https:\/\/community.smartsheet.com\/profile\/Jed%20Rusyniak","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-07\/ee\/78\/nee7870baf1e5832258cc68ae70eb674a.JPG","dateLastActive":"2023-05-12T16:12:55+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-17T15:55:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":136,"score":null,"hot":9,"url":"https:\/\/community.smartsheet.com\/discussion\/47256\/nested-if-statement-help-needed","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47256\/nested-if-statement-help-needed","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2019-05\/b7\/6f\/b76fd68138a32d2b850757bf67412b80.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2019-05-02 at 10.42.05 AM.png"},"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":47141,"type":"discussion","name":"IF\/AND Formula","excerpt":"I am trying to create a formula for when two approval columns have 'Approved' in their dropdowns to change two other dropdown columns. When \"APPROVAL Status - BOB\" = \"BOB - APPROVED\" AND When \"APPROVAL Status - JOEL\" = \"JOEL - APPROVED\" THEN \"Approved?\" column will change to \"Approved\" AND \"Issue Status\" column will change…","categoryID":322,"dateInserted":"2019-05-01T14:23:53+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-03T15:37:40+00:00","insertUserID":34321,"insertUser":{"userID":34321,"name":"kwheeler","url":"https:\/\/community.smartsheet.com\/profile\/kwheeler","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-08-06T12:29:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":34321,"lastUser":{"userID":34321,"name":"kwheeler","url":"https:\/\/community.smartsheet.com\/profile\/kwheeler","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-08-06T12:29:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":65,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/47141\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47141\/if-and-formula","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":218,"urlcode":"Construction","name":"Construction"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":285,"urlcode":"Solution Center","name":"Solution Center"},{"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":47036,"type":"discussion","name":"Formula help - adding criteria to LARGE?","excerpt":"Seeking some advice on how to make the LARGE function play nice with criteria and possibly, I'm thinking, the COLLECT function. Here's our set up: \"Master project sheet\" with: 1) \"Project name\" column 2) \"2019 total hours\" column which is a cross-sheet reference SUMIFS formula 3) \"Project priority score\" column which is a…","categoryID":322,"dateInserted":"2019-04-30T15:36:32+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2019-05-03T13:45:21+00:00","insertUserID":2723,"insertUser":{"userID":2723,"name":"Jaye Casciano","url":"https:\/\/community.smartsheet.com\/profile\/Jaye%20Casciano","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/OB4IJYEZR1XW\/nU83D7QKKE5D0.jpg","dateLastActive":"2023-07-11T17:01:57+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-17T16:01:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":391,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/47036\/formula-help-adding-criteria-to-large","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/47036\/formula-help-adding-criteria-to-large","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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
You are basically looking for duplicates. Try something like this in a flag type column:<\/p>

=IF(COUNTIFS([Assigned To]:[Assigned To], @cell = [Assigned To]@row, [Due Date]:[Due Date], @cell = [Due Date]@row)> 1, 1)<\/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":107659,"type":"question","name":"How to make Parent helper column reflect values based on Children values with multiple conditions","excerpt":"I am very new at using formulas with hierarchy. I simply cannot figure this out or find a similar situation to manipulate the formula. I want the Parent value in helper column Parent Proficiency to be based on CHILDREN values in Proficiency column Levels: Untrained In Process Trained Proficient Preceptor Parent helper row…","snippet":"I am very new at using formulas with hierarchy. I simply cannot figure this out or find a similar situation to manipulate the formula. I want the Parent value in helper column…","categoryID":322,"dateInserted":"2023-07-17T01:23:45+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T03:11:33+00:00","insertUserID":157192,"insertUser":{"userID":157192,"name":"decrom","url":"https:\/\/community.smartsheet.com\/profile\/decrom","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T03:18:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-07-17T12:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":33,"score":null,"hot":3379122318,"url":"https:\/\/community.smartsheet.com\/discussion\/107659\/how-to-make-parent-helper-column-reflect-values-based-on-children-values-with-multiple-conditions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107659\/how-to-make-parent-helper-column-reflect-values-based-on-children-values-with-multiple-conditions","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107659,"commentID":385499,"name":"Re: How to make Parent helper column reflect values based on Children values with multiple conditions","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385499#Comment_385499","dateInserted":"2023-07-17T03:11:33+00:00","insertUserID":112221,"insertUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-07-17T12:51:27+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\/8JA27C48KJIR\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-17T02:55:22+00:00","dateAnswered":"2023-07-17T02:36:04+00:00","acceptedAnswers":[{"commentID":385496,"body":"

Hey @decrom<\/a> <\/p>

Try this and see if it produces the expected result. Please be sure to test one 'In Process' with ZERO 'Untrained'. Was this what you expected?<\/p>

=IF(COUNT(CHILDREN(Proficiency@row)) > 0, IF(COUNT(CHILDREN(Proficiency@row)) = COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\"), \"Untrained\", IF(AND(COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\") >= 1, COUNT(CHILDREN(Proficiency@row)) > COUNTIFS(CHILDREN(Proficiency@row), \"Untrained\")), \"In Progress\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"In Process\") >= 1, \"In Process\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Trained\") >= 1, \"Trained\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Proficient\") >= 1, \"Proficient\", IF(COUNTIFS(CHILDREN(Proficiency@row), \"Preceptor\") >= 1, \"Preceptor\")))))))<\/p>

Please let me know what we need to tweak<\/p>

Kelly<\/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":107653,"type":"question","name":"Sum all children rows under parent","excerpt":"I have my project worksheets organized by year, quarter, month, project, etc. Is there a simple formula to sum all children rows one indent in? For example, all months sum up to the quarters which sum up to the year? I only want them to sum at one indent in from the immediate parent row.","snippet":"I have my project worksheets organized by year, quarter, month, project, etc. Is there a simple formula to sum all children rows one indent in? For example, all months sum up to…","categoryID":322,"dateInserted":"2023-07-16T12:23:14+00:00","dateUpdated":"2023-07-17T08:25:09+00:00","dateLastComment":"2023-07-17T15:20:29+00:00","insertUserID":163321,"insertUser":{"userID":163321,"name":"HH_BE1894","title":"Brand Manager","url":"https:\/\/community.smartsheet.com\/profile\/HH_BE1894","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T13:40:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-17T16:01:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":41,"score":1,"hot":3379120723,"url":"https:\/\/community.smartsheet.com\/discussion\/107653\/sum-all-children-rows-under-parent","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107653\/sum-all-children-rows-under-parent","format":"Rich","lastPost":{"discussionID":107653,"commentID":385566,"name":"Re: Sum all children rows under parent","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385566#Comment_385566","dateInserted":"2023-07-17T15:20:29+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-17T16:01:44+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-17T11:09:14+00:00","dateAnswered":"2023-07-17T09:37:31+00:00","acceptedAnswers":[{"commentID":385514,"body":"

Hi @HH_BE1894<\/a> <\/p>

I hope you're well and safe!<\/p>

You can add a so-called helper column that indicates a level and then use that in the calculation.<\/p>

Try something like this for the helper column. (change the column names as needed)<\/p>

I usually add a so-called helper column called Level with the formula below, and then you can use it to identify parents and use it for conditional formatting and more.<\/p>

=IF(COUNT(CHILDREN([Task Name]@row)) > 0, COUNT(ANCESTORS()) + 1)<\/p>

Would that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":1},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • How to find if someone is assigned to two tasks on the same day?

    Good morning, I have a sheet with due date in one column and team member assigned in another column. I want to ensure no one is assigned two tasks on the same day. I have exhausted my googling abilities and have come up empty. Any ideas here?

  • How to make Parent helper column reflect values based on Children values with multiple conditions

    I am very new at using formulas with hierarchy. I simply cannot figure this out or find a similar situation to manipulate the formula. I want the Parent value in helper column Parent Proficiency to be based on CHILDREN values in Proficiency column Levels: Untrained In Process Trained Proficient Preceptor Parent helper row…

  • Sum all children rows under parent

    I have my project worksheets organized by year, quarter, month, project, etc. Is there a simple formula to sum all children rows one indent in? For example, all months sum up to the quarters which sum up to the year? I only want them to sum at one indent in from the immediate parent row.