Percentage of check boxes - Need to exclude blank cells

Blake H

Hello!

I am working on a formula to calculate the percentage of boxes checked. This formula is currently working when I set the range to exclude the blank cells. However, I need to allow space for more rows to be added in the course name column in the future. I would like to make this formula to exclude those blank cells so that when new rows are added to the "course name" column they will be accounted for.

Paul Newcome

Try using the CHILDREN function for your ranges.

=COUNTIFS(CHILDREN([Course Completed]@row), @cell = 1) / COUNT(CHILDREN([Course Name]@row))

Paul Newcome

Try using the CHILDREN function for your ranges.

=COUNTIFS(CHILDREN([Course Completed]@row), @cell = 1) / COUNT(CHILDREN([Course Name]@row))

Blake H

@Paul Newcome, you rock my friend. This worked great!

Blake H

@Paul Newcome, I have one more question for you. I need to do a similar calculation with the Foundation Course Completion % column.

If course level is "Foundational" AND "Course Completed" is checked

Compared to

If course level is "Foundational" AND "Course Completed" is not checked

This would exclude counting any children where the above criteria are not true.

I am currently using the following formula =

=COUNTIF([Course Completed]9:[Course Completed]12, 1) / COUNT([Course Completed]9:[Course Completed]12)

It is working, but I need to account for future rows being added.

Any thoughts?

Paul Newcome

You would do the same thing and use the CHILDREN function.