Formula to count tasks completed (x out of x).
Hi Smartsheet Community,
I have used the remote onboarding template set to help track how many tasks have been completed. I am now working on a new sheet where my requirements vary and I need to count the completed tasks in a different manner by using text instead of a checkbox. I am fairly new to this part of Smartsheets, so any help in understanding how to get to the solution (or use a summary sheet) is greatly appreciated!
Here is a link to the remote onboarding template. Instead of a checklist column labeled "done", I now have a column labeled "task status" that counts the percentage of the project completed when a cell is marked as complete. I am trying to make a new column labeled "count of tasks completed" to show "x out of total tasks" completed.
Below are the formulas I am using on the first, second, and third rows for the column labeled "% Complete"
1st row: =AVG(CHILDREN())
2nd row: =AVG(CHILDREN())
3rd row: =COUNTIF(CHILDREN(Status@row), ="Complete") / COUNT(CHILDREN(Status@row))
Thank you in advance!
Best Answers
-
Hey @Namaste03
You've already done the heavy lifting for the formulas. You can take your 3rd row formula to use it in your x of y completed formula. Since you will be mixing texts and numbers together, the VALUE function will alert smartsheet of which characters are numeric.
="x out of total tasks"
=VALUE(COUNTIF(CHILDREN(Status@row), ="Complete")) + " completed out of "+VALUE(COUNT(CHILDREN(Status@row)))+" total tasks"
Does this work for you?
Kelly
-
It works! Thank you Kelly.
Answers
-
Hey @Namaste03
You've already done the heavy lifting for the formulas. You can take your 3rd row formula to use it in your x of y completed formula. Since you will be mixing texts and numbers together, the VALUE function will alert smartsheet of which characters are numeric.
="x out of total tasks"
=VALUE(COUNTIF(CHILDREN(Status@row), ="Complete")) + " completed out of "+VALUE(COUNT(CHILDREN(Status@row)))+" total tasks"
Does this work for you?
Kelly
-
It works! Thank you Kelly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!