Summary Fields: Percentage of Completed Checkboxes

I'm very new to Smartsheet and hoping someone can help me. I have created a checklist, with different categories divided up by parent rows, and the tasks listed as children under the categories. I have a checkbox column and each task (in the children rows) has a checkbox. I have figured out how to calculate the percentage of completed tasks in each category in the parent rows, but I would like to be able to calculate the overall percentage of completed tasks in a sheet summary field. Is this possible? I cannot find a formula that will calculate all of the children rows together. I have included a screenshot to demonstrate what I mean!

Screen Shot 2019-10-16 at 11.10.17 AM.png

Find more posts tagged with

Construction

Sheets

API and Developers

Formulas

Reports

Services

Support

Community Platform

Product Development

Conditional Formatting

Project Management

Cell linking

Comments

Andrée Starå

Hi Allison,

Welcome to the Community and the wonderful world of Smartsheet!

I'd recommend turning on the automatic % Complete / Duration and more in the Project Settings and then link from that to the Sheet Summary.

Would that work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Andrée Starå

More info: https://help.smartsheet.com/articles/765755-modifying-project-settings

Paul Newcome

I am assuming you are using the CHILDREN function in each of the Parent rows.

Try replacing that with the DESCENDANTS function and reference row 1.

If that doesn't work, there are a few other options. Let me know.

Allison Caskey

Hi Andrèe,

Thanks so much for your quick response and your welcome!

From the article you linked, my understanding is that to use the automatic % complete calculations, I would need to create a % complete column in which the checklist user would manually input their % completion of each task, and it would calculate in the parent rows based on that. Is that correct?

I'm trying to use it more like a traditional checklist where they can tick off activities, but don't have to enter too much information. I'm not sure if it's possible, but was hoping to find a formula that would calculate the completion percentage based off of the checkboxes.

I really appreciate you providing your insight!

Allison Caskey

Hi Paul,

Thank you so much for providing some insight! The formula that I put in the parent rows is the following:

=ROUND(IF(COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()) > 0, COUNTIF(DESCENDANTS(), 1) / COUNT(DESCENDANTS()), 0) * 100, 0) + "%"

From my understanding of your suggestion, in my summary field, I tried the following formulas:

=ROUND(IF(COUNTIF(DESCENDANTS(COMPLETED1), 1) / COUNT(DESCENDANTS(COMPLETED1)) > 0, COUNTIF(DESCENDANTS(COMPLETED1), 1) / COUNT(DESCENDANTS(COMPLETED1)), 0) * 100, 0) + "%"

This one gave me a divide by zero error, but on my sheet the first parent row is on the second row instead of the first row, so I also tried this formula:

=ROUND(IF(COUNTIF(DESCENDANTS(Completed2), 1) / COUNT(DESCENDANTS(Completed2)) > 0, COUNTIF(DESCENDANTS(Completed2), 1) / COUNT(DESCENDANTS(Completed2)), 0) * 100, 0) + "%"

This returned the % of tasks completed from the children under that parent row, but not the full checklist.

I also tried this formula, hoping it would reference the full column:

=ROUND(IF(COUNTIF(DESCENDANTS([Completed]), 1) / COUNT(DESCENDANTS([Completed]) > 0, COUNTIF(DESCENDANTS([Completed]), 1) / COUNT(DESCENDANTS([Completed])), 0) * 100, 0) + "%"

This returned the unparseable error.

Is there something I'm doing incorrectly? I really appreciate your help!

Paul Newcome

I took another look at your screenshot and noticed where my suggestion was actually incorrect.

The DESCENDANTS function doesn't work for this case because each of your parents are on the first level of hierarchy.

Let's try something similar to your final try. We will drop the DESCENDANTS function and reference the entire column. For the total count of all children boxes, we will count all cells within the column that are NOT text and compare that to the count of checked boxes.

=ROUND(IF(COUNTIFS(Completed:Completed), 1) / COUNTIFS(Completed:Completed, NOT(ISTEXT(@cell))) > 0, COUNTIFS(Completed:Completed), 1) / COUNTIFS(Completed:Completed, NOT(ISTEXT(@cell))), 0) * 100, 0) + "%"

Allison Caskey

That formula returned an unparseable error, but you made me think to try this one and it worked!

=ROUND(IF(COUNTIF(CHILDREN(COMPLETED:COMPLETED), 1) / COUNT(CHILDREN(COMPLETED:COMPLETED)) > 0, COUNTIF(CHILDREN(COMPLETED:COMPLETED), 1) / COUNT(CHILDREN(COMPLETED:COMPLETED)), 0) * 100, 0) + "%"

Thank you so much for helping me figure this out!

Paul Newcome

That's what I get for trying to copy/paste instead of rewriting. I closed off two of the COUNTIFS early. Sorry about that.

I'm glad you were able to get something working. Happy to help!

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
Sheet Summary Formula
I am looking to count how many letters we have issued within each month, this is by going by the modified date, these are the formulas i have tried as i want to start this from August: =COUNTIFS(Modified:Modified, MONTH(@cell ) = 8, Modified:Modified, YEAR(@cell ) = 2025) However, it comes up with an error. Any ideas…