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

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

Sign In or Register to comment.