# Percentage of check boxes - Need to exclude blank cells

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.

• ✭✭✭✭✭✭

Try using the CHILDREN function for your ranges.

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

• ✭✭✭✭✭✭

Try using the CHILDREN function for your ranges.

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

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

• edited 09/05/24

@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?

• ✭✭✭✭✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!