I need help so that I can calculate a percentage of completion

Maria Ciobanu
Maria Ciobanu ✭
inFormulas and Functions

Guys, I need some help. I am struggling with calculating the percentage of completion of the whole sheet based on how many tasks are Complete in the Status column, or maybe better said to calculate the completion percentage based on the Parents Status in the Status column.


Thank you and I am waiting for your advice.

Tags:
· Share on Facebook Share on Twitter

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@Maria Ciobanu

    I hope you're well and safe!

    Have you looked at using the built-in automatic % Complete Feature?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
@Mike Wilday<\/a> I got it figured out. I totaled the columns first - then added the totals together using this formula: =SUMIFS(TOTAL:TOTAL, [Rental Start Date]:[Rental Start Date], AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2022)) Thanks again for your help. I'm all set!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-06 09:40:53","updateUser":{"userID":91566,"name":"Genevieve P.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/T4YLN045Q7TF\/n81H11L2YPG9T.jpg","dateLastActive":"2023-01-06T12:41:58+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}}},"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":99316,"type":"question","name":"Collect Most Current Per Project","excerpt":"We build out contracts and contract MODs on a spreadsheet, the single spreadsheet contains all contracts and MODs across all projects. I'm needing to create a column with a function that reports the most current difference between the original contract and sum of any MODs, per project, per company. Columns involved so…","categoryID":322,"dateInserted":"2023-01-05T19:05:00+00:00","dateUpdated":"2023-01-05T19:10:47+00:00","dateLastComment":"2023-01-06T09:51:08+00:00","insertUserID":156527,"insertUser":{"userID":156527,"name":"Tim Hanson","url":"https:\/\/community.smartsheet.com\/profile\/Tim%20Hanson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-01-05T21:56:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":156527,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/T4YLN045Q7TF\/n81H11L2YPG9T.jpg","dateLastActive":"2023-01-06T12:41:58+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3345945368,"url":"https:\/\/community.smartsheet.com\/discussion\/99316\/collect-most-current-per-project","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99316\/collect-most-current-per-project","format":"Rich","lastPost":{"discussionID":99316,"commentID":356141,"name":"Re: Collect Most Current Per Project","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/356141#Comment_356141","dateInserted":"2023-01-06T09:51:08+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/T4YLN045Q7TF\/n81H11L2YPG9T.jpg","dateLastActive":"2023-01-06T12:41:58+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-01-06T09:51:11+00:00","dateAnswered":"2023-01-05T21:52:55+00:00","acceptedAnswers":[{"commentID":356101,"body":"

I was approaching this all wrong. Here is the solution I found.<\/p>

=IF(MAX(COLLECT(Created:Created, Project:Project, Project@row, Company:Company, Company@row)) = Created@row, [New Contract Sum]@row - [Original Contract Amount]@row, \"//www.santa-greenland.com/community/discussion/80166/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-06 09:51:11","updateUser":{"userID":91566,"name":"Genevieve P.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/T4YLN045Q7TF\/n81H11L2YPG9T.jpg","dateLastActive":"2023-01-06T12:41:58+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}}},"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":99329,"type":"question","name":"SUMS and IF Formulas","excerpt":"Hi I need a formula that sums up the value of children that are checked off in my sheet. I hope i am making sense. So i have a sheet 4 columns, qty x cost. this produces a sum then a column that has check boxes, i only want the formula to sum up those those cells that have been checked off. I hope I am explaining it…","categoryID":322,"dateInserted":"2023-01-05T21:51:37+00:00","dateUpdated":null,"dateLastComment":"2023-01-06T00:11:27+00:00","insertUserID":156539,"insertUser":{"userID":156539,"name":"LisaPetersonBW ","url":"https:\/\/community.smartsheet.com\/profile\/LisaPetersonBW%20","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pWDBKQryqnY!xKDAsQ7hLHQ!dPBBqXJsIFM","dateLastActive":"2023-01-06T00:10:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":156539,"lastUser":{"userID":156539,"name":"LisaPetersonBW ","url":"https:\/\/community.smartsheet.com\/profile\/LisaPetersonBW%20","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pWDBKQryqnY!xKDAsQ7hLHQ!dPBBqXJsIFM","dateLastActive":"2023-01-06T00:10:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":26,"score":null,"hot":3345921784,"url":"https:\/\/community.smartsheet.com\/discussion\/99329\/sums-and-if-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/99329\/sums-and-if-formulas","format":"Rich","lastPost":{"discussionID":99329,"commentID":356114,"name":"Re: SUMS and IF Formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/356114#Comment_356114","dateInserted":"2023-01-06T00:11:27+00:00","insertUserID":156539,"insertUser":{"userID":156539,"name":"LisaPetersonBW ","url":"https:\/\/community.smartsheet.com\/profile\/LisaPetersonBW%20","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pWDBKQryqnY!xKDAsQ7hLHQ!dPBBqXJsIFM","dateLastActive":"2023-01-06T00:10:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-01-06T00:11:45+00:00","dateAnswered":"2023-01-05T23:37:52+00:00","acceptedAnswers":[{"commentID":356108,"body":"

=if(checkbox@row=1,qty@row*cost@row,\"//www.santa-greenland.com/community/discussion/80166/\")\n<\/pre>
You'll need to use the column names in place of what I wrote. After it is working how you want you can right-click on the cell and convert it to a column formula so it works on the whole column.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2023-01-06 00:11:45","updateUser":{"userID":156539,"name":"LisaPetersonBW ","url":"https:\/\/community.smartsheet.com\/profile\/LisaPetersonBW%20","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pWDBKQryqnY!xKDAsQ7hLHQ!dPBBqXJsIFM","dateLastActive":"2023-01-06T00:10:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">