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

index match using multiple critieria
Hello, I want to use a formula using INDEX/ MATCH Function using multiple criteria to get a single value I am trying to get a value of Column Description from File 2 based on matching Source System ,Table name and Attribute Field Index ( as the column names may be different ) The formula Im using gets me an INVALID…
How can I use Conditional Formatting for date in the past or future when a formula created the date
Hi Everyone, Thank you for your time in reading this. How can I use Conditional Formatting for date in the past or future when a formula created the date? The black columns are different dates generated. The 2 yellow columns are then selecting the relevant date based on different criteria. The white columns are me trying…
Auto Check a box after 24 hours
I have tried to get smartsheet to auto check a box 24 hours after a row is created and column "Task Started" is not checked. =IF(AND([Task Started]@row <> 1, TODAY() - Created@row >= 1), 1) This returns #UNPARSEABLE. Any help would be appreciated.