Percentage Complete Parent/Child

Options

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?

• ✭✭✭✭✭✭
Options

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.

«1

• ✭✭✭✭✭✭
edited 05/11/22
Options

Here's an example:

Status formula:

=COUNTIF(CHILDREN(Status@row), ="Assigned") / COUNT(CHILDREN(Status@row))

• Options

@Matt C. That is exactly what I want to do but when I put that formula in the parent row I get #UNPARSEABLE

• Options

Is that because Assigned and Completed are dropdowns?

• Options
• ✭✭✭✭✭✭
Options

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?

• Options
• ✭✭✭✭✭✭
Options

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.

• Options

Yes this was it! Thank you so much for the quick and and the quick help. Now I can keep working!

• Options

@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?

• ✭✭✭✭✭✭
Options

=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.

• Options

Ok got it. I have got to get my Excel brain to think another way. Thank you again!

• Options

@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))

• ✭✭✭✭✭✭
Options

=COUNTIF(CHILDREN([African Grant]@row), ="Assigned") / COUNTIF(CHILDREN([African Grant]@row), <>"n/a")

• Options

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?

Options

Are you looking for the Average?

Try:

=AVG(CHILDREN())

Cheers,

Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!