Summary Fields: Percentage of Completed Checkboxes

Allison Caskey

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!

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