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

Nikki McIntosh

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:

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

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

Thanks in advance!

Genevieve P.

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], <>""))

FULL FORMULA:

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!

Cheers,

Genevieve