Percentage Complete Parent/Child
I have several child rows that are marked either Assigned or Completed. I need the parent row to tell me what the percentage complete is.
So somehow counting the total number of total child rows and then counting the number of Assigned rows and the number of Completed rows then given me a percentage of Assigned rows.
How do I do that?
Best Answer
-
For Augusta Hohbach column:
=COUNTIF(CHILDREN([Augusta Hohbach]@row), ="Assigned") / COUNT(CHILDREN([Augusta Hohbach]@row))
For African Grant column:
=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNT(CHILDREN([African Grant]@row))
For Taylor Rivera column:
=COUNTIF(CHILDREN([Taylor Rivera]@row), ="Assigned") / COUNT(CHILDREN([Taylor Rivera]@row))
Make sure these three columns are set as percentage so your formula will display a percentage. It wont affect the drop down selections any, just the number value of your formulas.
Answers
-
Here's an example:
Status formula:
=COUNTIF(CHILDREN(Status@row), ="Assigned") / COUNT(CHILDREN(Status@row))
-
@Matt C. That is exactly what I want to do but when I put that formula in the parent row I get #UNPARSEABLE
-
Is that because Assigned and Completed are dropdowns?
-
Mine was also a dropdown single-select column. It could be because the column you have your Child rows in is called something other than Status. What's the name of the column with your Assigned and Completed statuses in or can you provide a screenshot?
-
For Augusta Hohbach column:
=COUNTIF(CHILDREN([Augusta Hohbach]@row), ="Assigned") / COUNT(CHILDREN([Augusta Hohbach]@row))
For African Grant column:
=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNT(CHILDREN([African Grant]@row))
For Taylor Rivera column:
=COUNTIF(CHILDREN([Taylor Rivera]@row), ="Assigned") / COUNT(CHILDREN([Taylor Rivera]@row))
Make sure these three columns are set as percentage so your formula will display a percentage. It wont affect the drop down selections any, just the number value of your formulas.
-
Yes this was it! Thank you so much for the quick and and the quick help. Now I can keep working!
-
@Matt C. One more question on this... If I want to average the 3 percentages to an overall average in a column to the right? How does that work?
-
=AVG([Augusta Hohbach]4, [African Grant]4, [Taylor Rivera]4)
The above formula assumes these were on Row 4. Replace the number 4 with whichever row they're really on. Format the new column as percentage will be needed, I think.
-
Ok got it. I have got to get my Excel brain to think another way. Thank you again!
-
@Matt C. Hi...found another thing. How do I add an error option for the average formula here? some cells will need to be left blank because they don't apply to a specific column, so I need to make it so the n/a is ignored. Possible?
=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNT(CHILDREN([African Grant]@row))
-
=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNTIF(CHILDREN([African Grant]@row), <>"n/a")
-
Hi Team,
I've tried everything on here. Still can't get it to work.
I have % Complete using (0%, 25%, 50%, 75%, 100%) however the parent doesn't calculate the % of the rows below it. How can this be calculated for the parent?
-
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!