Calculating a percentage of lines complete based on weight value

Kayla Wootten

I would like to input a formula that shows the percentage of the parts that are complete, based on weight instead of quantity.

My current formula setup is =(SUMIF(CHILDREN(), [Complete]= 1)) / (SUM(CHILDREN()))

I need to know what to type after the [Complete] in order to make the formula parsable. If I put in a number such as 1 (ex [Complete]1 ), it becomes parsable but does not give accurate percentages. 

If the formula was working correctly, it should currently be giving a value of 0.75.

Mark Cronk

Hi @Kayla Wootten ,

Try:

=SUMIF(CHILDREN([Complete]@row), = 1, Children()) / (SUM(CHILDREN())

Work?

Mark

Kayla Wootten

Hello @Mark Cronk ,

Yes it does, thank you!

I now have a second question.

We have a smartsheet we use that has multiple parent rows, each with children under them, like the example I gave a snip of. We want to implement this formula for every parent row. What would your thoughts be on that?

Thank you!

Mark Cronk

Hi @Kayla Wootten ,

To identify a row as a parent, add a text/number helper column [Tier] with the column formula =COUNT(ANCESTORS()). Parent rows will be =0, Children will be 1 (or greater if you have multiple tiers)

=IF(TIER@row=0, SUMIF(CHILDREN([Complete]@row), = 1, Children()) / (SUM(CHILDREN()), "")

Placing this formula in your Complete column is going to prevent users from checking complete in the child rows unless you're using a formula to determine Comlplete. If you're using a formula add it in place of the "" at the end and all will be fine. Otherwise you'll need to create another column to mark parents complete.

Help?

Mark