Count check boxes in child rows and convert to percentage

I set up a sheet with a checkbox column called "Confirm complete". I started with 5 children under the main task and got this formula to work in counting the check boxes and displaying a percent complete for that group of tasks:

=COUNTIF(CHILDREN(), 1) / 5 * 100 + "%"

The project has now expanded and there are two sections each with 5 subtasks and now there is a parent row above that. I tried using the same formula and just changing the denominator to 10 instead of 5. But it is not recognizing any of the check boxes when they are marked. Trying to solve for the yellow cell in the screen below. should read 20% ( 2 out of 10 sub tasks completed)

Find more posts tagged with

Accepted answers

All comments

Ramzi K

@Randy Van Winkle

Here's one way to do it. This is an example similar to yours.

Use this formula in the parent cells in the Complete column:

=COUNTIF(CHILDREN(), true) / COUNT(CHILDREN()) * 100 + "%"

Use this formula in the grandparent cell in the Complete column:

=COUNTIF(DESCENDANTS(), true) / (COUNT(DESCENDANTS()) - COUNT(CHILDREN())) * 100 + "%"

This technique also provides you a way to have as many child/grandchild rows as you want.

I hope this helps.

Cheers,

Ramzi

Randy Van Winkle

Perfect! thanks

Joe Costa

Is there a way to use this solution but force the % to round up/down? The decimal decrease button does not seem to work, I assume because the column is a check box... Otherwise this works great

Merissa Gilstrap

Good morning Joe, I was wondering if you found a solution to this?

Is there a way to use this solution but force the % to round up/down? The decimal decrease button does not seem to work, I assume because the column is a check box... Otherwise this works great

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.