Calculating a percentage of lines complete based on weight value

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.

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.