Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template查看100 +公式,包括词汇的夏娃ry 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

0, FIND(\"exist\", LOWER(@cell)) > 0), @{Implementation Rollup Work Start}, IFERROR(WEEKNUMBER(@cell), [1\/7\/19]$1) <= [1\/7\/19]$1, {Implementation Rollup Work Finish}, IFERROR(WEEKNUMBER(@cell), [1\/7\/19]$1) >= [1\/7\/19]$1, {Implementation…","categoryID":322,"dateInserted":"2019-04-09T11:53:33+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2020-10-22T16:48:16+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-17T15:25:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":124108,"lastUser":{"userID":124108,"name":"Cat Werbeck","url":"https:\/\/community.smartsheet.com\/profile\/Cat%20Werbeck","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zAjcs-xjxyY!7rFcOyWHXPo!XvnrJU9jDp7","dateLastActive":"2020-12-23T20:01:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":275,"score":null,"hot":11,"url":"https:\/\/community.smartsheet.com\/discussion\/45446\/formula-woes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/45446\/formula-woes","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":72481,"type":"question","name":"Checkbox formula with percentage","excerpt":"Smartsheet guru's-- I'm still learning how to use all of Smartsheet's assets. I am trying to show a percentage for checked boxes. Can someone help me with the formula part? Thank you!","categoryID":322,"dateInserted":"2020-10-22T15:02:48+00:00","dateUpdated":null,"dateLastComment":"2020-10-22T16:15:53+00:00","insertUserID":127896,"insertUser":{"userID":127896,"name":"Tara Knox","url":"https:\/\/community.smartsheet.com\/profile\/Tara%20Knox","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nW1W8KHNFR845.png","dateLastActive":"2020-12-18T12:46:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-14T17:07:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":1813,"score":null,"hot":10,"url":"https:\/\/community.smartsheet.com\/discussion\/72481\/checkbox-formula-with-percentage","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72481\/checkbox-formula-with-percentage","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q2WBJ9I6RFHQ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T16:18:07+00:00","dateAnswered":"2020-10-22T15:07:20+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":72457,"type":"question","name":"Formula to create a number assignment catalog","excerpt":"Hello, I am trying to create a number assignment catalog that is automated to speak to a smartsheet that has my full collection of products. The goal is when creating a new product anyone could fill out a form select the category numbers that will define the department and then select a a number that will identify the…","categoryID":322,"dateInserted":"2020-10-21T21:36:48+00:00","dateUpdated":"2020-10-21T21:38:06+00:00","dateLastComment":"2020-10-22T15:16:47+00:00","insertUserID":127490,"insertUser":{"userID":127490,"name":"Aaron Olsen","url":"https:\/\/community.smartsheet.com\/profile\/Aaron%20Olsen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-06-24T20:21:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":127490,"lastUserID":127490,"lastUser":{"userID":127490,"name":"Aaron Olsen","url":"https:\/\/community.smartsheet.com\/profile\/Aaron%20Olsen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-06-24T20:21:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":157,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/72457\/formula-to-create-a-number-assignment-catalog","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72457\/formula-to-create-a-number-assignment-catalog","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T15:15:44+00:00","dateAnswered":"2020-10-21T23:50:52+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":72473,"type":"question","name":"Pulling data from a sheet","excerpt":"Good Morning, I have a report that I do monthly that records the data from the following sheet. At the end of the month I usually filter columns and manually count errors for each responsible party and move them to a another sheet that records each persons errors for the whole year. I record data for about 30 individuals…","categoryID":322,"dateInserted":"2020-10-22T11:59:01+00:00","dateUpdated":null,"dateLastComment":"2020-10-22T14:01:38+00:00","insertUserID":127701,"insertUser":{"userID":127701,"name":"Austin Bell","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Austin%20Bell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-02-01T12:24:35+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-14T17:07:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":1145,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/72473\/pulling-data-from-a-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72473\/pulling-data-from-a-sheet","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/N3AZYF6ZOH18\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T14:00:42+00:00","dateAnswered":"2020-10-22T12:53:46+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":72469,"type":"question","name":"sequential number for each parent row - column formula","excerpt":"Hello im trying to generate a sequential number for each parent row. I can do this using an helper column called parent that flags each parent record with 1 then the formula @row, 1) this won't convert to a column formula because of the explicit row references used to define the range. can it be done. any help appreciated…","categoryID":322,"dateInserted":"2020-10-22T10:08:44+00:00","dateUpdated":null,"dateLastComment":"2020-10-22T13:35:29+00:00","insertUserID":127885,"insertUser":{"userID":127885,"name":"DHU - PMO","url":"https:\/\/community.smartsheet.com\/profile\/DHU%20-%20PMO","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2020-11-12T06:22:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-14T17:07:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":133,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/72469\/sequential-number-for-each-parent-row-column-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72469\/sequential-number-for-each-parent-row-column-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":72462,"type":"question","name":"Accounting for the word \"Perpetual\" in a date field when there is no end date","excerpt":"I am currently running this formula: =[Contract Expiration Date]@row - [Notify days to renew\/cancel]@row to calculate the date when we need to review a contract. It essentially takes the end date of the contract and subtracts the numbers of days specified in the contract if you want to cancel. We have some contracts with…","categoryID":322,"dateInserted":"2020-10-22T01:10:19+00:00","dateUpdated":null,"dateLastComment":"2020-10-22T13:34:19+00:00","insertUserID":127695,"insertUser":{"userID":127695,"name":"Emily McNeeley","url":"https:\/\/community.smartsheet.com\/profile\/Emily%20McNeeley","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!26jRP0ygZBY!wulddzL-Obw!pJP7L_r-ZLW","dateLastActive":"2022-10-23T10:29:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-07-14T17:07:32+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":88,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/72462\/accounting-for-the-word-perpetual-in-a-date-field-when-there-is-no-end-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72462\/accounting-for-the-word-perpetual-in-a-date-field-when-there-is-no-end-date","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-22T13:29:27+00:00","dateAnswered":"2020-10-22T13:03: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":72333,"type":"question","name":"Column Formula not selectable as Gantt Label","excerpt":"Hi Folks, Is this a bug? For Gantt chart labels, I use a column that concatenates and formats the task and end dates; call this column \"Gantt Label\". I select this as my Gantt label and it looks great. To prevent my formula from getting corrupted or broken, I convert \"Gantt Label\" to a Column Formula. Here's where it gets…","categoryID":322,"dateInserted":"2020-10-17T00:40:44+00:00","dateUpdated":null,"dateLastComment":"2020-10-21T21:39:02+00:00","insertUserID":3351,"insertUser":{"userID":3351,"name":"Jimmy Poon","url":"https:\/\/community.smartsheet.com\/profile\/Jimmy%20Poon","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hAdDoda45zw!pEjAqe7WqSQ!b0sos148Vrg","dateLastActive":"2020-11-10T21:28:04+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":3,"countViews":183,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/72333\/column-formula-not-selectable-as-gantt-label","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/72333\/column-formula-not-selectable-as-gantt-label","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-10-21T21:46:17+00:00","dateAnswered":"2020-10-21T21:39:02+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}]}],"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
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-17T15:25:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":40,"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-17T15:25:54+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":[]},{"discussionID":107613,"type":"question","name":"Add pictures inside a cells which look like a form - Workapps","excerpt":"Hi everyone, I have just discover a thing in the Workapps. I made the same as all the other date : open my sheet (grid view) on my workapps. However, this time the sheet does'nt look likes a grid view but like card and we can add pictures inside a cells. You can see that in the picture that I add. If anydbody have a…","snippet":"Hi everyone, I have just discover a thing in the Workapps. I made the same as all the other date : open my sheet (grid view) on my workapps. However, this time the sheet does'nt…","categoryID":322,"dateInserted":"2023-07-14T10:56:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T10:23:13+00:00","insertUserID":151421,"insertUser":{"userID":151421,"name":"lea.s6","url":"https:\/\/community.smartsheet.com\/profile\/lea.s6","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!G69Wn8Gn53s!xLDXMWmx7Oc!BP_1d2Auq2F","dateLastActive":"2023-07-17T10:57:43+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-17T14:09:03+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":48,"score":null,"hot":3378925193,"url":"https:\/\/community.smartsheet.com\/discussion\/107613\/add-pictures-inside-a-cells-which-look-like-a-form-workapps","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107613\/add-pictures-inside-a-cells-which-look-like-a-form-workapps","format":"Rich","lastPost":{"discussionID":107613,"commentID":385521,"name":"Re: Add pictures inside a cells which look like a form - Workapps","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385521#Comment_385521","dateInserted":"2023-07-17T10:23:13+00:00","insertUserID":8888,"insertUser":{"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-17T14:09:03+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\/ZA10DMWH9B2G\/image-2023-07-14-11-46-59.jpeg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Image 2023-07-14 11-46-59.jpeg"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-17T10:19:13+00:00","dateAnswered":"2023-07-17T10:08:37+00:00","acceptedAnswers":[{"commentID":385518,"body":"

@lea.s6<\/a> when a page is loaded in WorkApps the setting related to how the page shows on a mobile can be amended here:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"},{"commentID":385520,"body":"

Thanks for your answer !<\/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":[]}],"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 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.

  • Add pictures inside a cells which look like a form - Workapps

    Hi everyone, I have just discover a thing in the Workapps. I made the same as all the other date : open my sheet (grid view) on my workapps. However, this time the sheet does'nt look likes a grid view but like card and we can add pictures inside a cells. You can see that in the picture that I add. If anydbody have a…