Help with Formula
Hi,
In Description column row 2, I am trying to calculate the percentage of completed items divided by the total number of child items. I have a helper column as well that indicates the count of child items for Projections, Planning, Action.
For example, in the screenshot below for projections I am trying to calculate the number of completed items (0) divided by the total child count (2)
Does anyone know of a formula that could help with this?
Answers
-
try this:
=COUNTIF(CHILDREN(Status:Status), "Completed") / [Child Count]@row
Sincerely,
Jacob Stey
-
-
Hi @Jessica Suarez,
You are getting the #Unparseable error because you have an extra ":" (colon) in the formula, it should be the following.
=COUNTIF(CHILDREN(Status:Status), "Complete") / [Child Count]@row
Having said that, I am not convinced that formula is going to give the results you are looking for. The formula is going to count every task marked complete in it's calculation, regardless of hierarchy. So, in your example above, the description is going to say "3.5" because the formula equates "7 / 2."
Please let us know if things are working as expected.
Hope this helps,
Dave
-
You're right. Good eye.
I believe the correct formula would be
=COUNTIF(CHILDREN(Status2), "Completed") / COUNT(CHILDREN(Status2))
Where 2 would be the parent row of the children you're trying to count.
You can also get away with @row in your case, I believe
=COUNTIF(CHILDREN(Status@row), "Completed") / COUNT(CHILDREN(Status@row))
Sincerely,
Jacob Stey
-
Hello @DKazatsky2
You are correct. it's not achieving the results that I am looking for.
I was more so looking for the formula to calculate the completed children (2) over the count children (2)
May I ask why it's calculating in every single child item and not just considering the child items of the parent row that I'm on?
-
It was calculating all the children because it was given the entire column as the range instead of just the children of the row - @SteyJ corrected that with his revised formula.
I believe this formula is closer to what you are looking for.
=COUNTIF(CHILDREN(Status@row), "Complete") / COUNT(CHILDREN(Task@row))
Note: This does not count the task "Formalize findings with Brandon" as a child of "Projections" so be careful to setup hierarchy to show what you intend.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!