Summary Fields: Percentage of Completed Checkboxes

Allison Caskey
edited 12/09/19 in Formulas and Functions

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

Comments

  • Andrée Starå
    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

    work-bold


     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    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
    edited 10/18/19

    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!

  • 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
    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) + "%"

  • 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
    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! yes

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!