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:
=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!
Answers
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!
