Don't include parent rows in checkbox complete roll-up percentage


Hello. I'm new to formulas and need help. I'm trying to figure out how to get the total % complete for a column of check boxes. We need to know the % complete by milestone (which we figured out) AND the total % complete. We use parent rows as headers that should not be included in this total. I had a formula that sort of worked, but lost it. This is the formula I used for the milestone tracking:


How would I write a formula that would not include parent rows?

Thanks in advance!


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Nikki,

    I would actually build this out under your TASK column so that you can return a number. It will be easier to reference your checkbox column this way as well.

    Based on this image, it looks like none of your parent rows have data/content in the column Task / Request Type. This is great, as it means we can use the blank cell in this column to indicate if it's a Parent row or not! Then we can build a COUNTIFS (plural) statement to look for two criteria: a checkbox in the Complete column and not blank in the Task/Request Type column.

    So our first formula will look for a count of checked boxes in child rows:

    =COUNTIFS(Complete:Complete, 1, [Task/Request Type]:[Task/Request Type], <>"")

    Then the second half of our formula will need to calculate how many rows there are that need a checkbox... so we add together the COUNT of 1's and 0's in that column:

    / (COUNTIFS(Complete:Complete, 1, [Task/Request Type]:[Task/Request Type], <>"") + COUNTIFS(Complete:Complete, 0, [Task/Request Type]:[Task/Request Type], <>""))


    Try putting this where you currently have "Total % Complete" typed out:

    =COUNTIFS(Complete:Complete, 1, [Task/Request Type]:[Task/Request Type], <>"") / (COUNTIFS(Complete:Complete, 1, [Task/Request Type]:[Task/Request Type], <>"") + COUNTIFS(Complete:Complete, 0, [Task/Request Type]:[Task/Request Type], <>""))

    This will give you a decimal. Click on the cell to highlight it, then use the Percent Format from the toolbar menu at the top of the sheet to change it into a percentage. This is how I set up my sheet, so you can see what I mean:

    Let me know if this works for you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!