# Calculating percentage of check boxes checked in a column

Options
✭✭✭

Hi,

I am using the below formula within the sheet summary section to calculate the percentage of check boxes checked in the Completed column. I am getting an incorrect percentage though as the formula is taking into account the cells that have no empty check boxes (no text in the rows).

=COUNTIF(Completed:Completed, 1) / COUNT(Completed:Completed)

I was using =COUNTIF(Completed1:Completed42, 1) / COUNT(Completed1:Completed42) which worked fine until row 43 was added as the formula was only accounting for 1 to 42.

Appreciate any help.

• ✭✭✭✭✭✭
Options

You should be able to use =COUNTIF(Completed:Completed,1)/COUNTM(Completed:Completed), this would account for not only row 43, but also the addition of future rows.

The other option I have used in the past would be =COUNTIF(Completed\$1:Completed@row)/COUNTM(Completed\$1:Completed@row)

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

• ✭✭✭✭✭✭
Options

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

• ✭✭✭✭✭✭
Options

You should be able to use =COUNTIF(Completed:Completed,1)/COUNTM(Completed:Completed), this would account for not only row 43, but also the addition of future rows.

The other option I have used in the past would be =COUNTIF(Completed\$1:Completed@row)/COUNTM(Completed\$1:Completed@row)

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

• ✭✭✭
Options

Hi Colleen,

That worked perfect, thank you!

• ✭✭✭✭✭✭
Options

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

• ✭✭✭
Options

Hi Colleen,

I'm having an issue with the formula =COUNTIF(Completed:Completed,1)/COUNTM(Completed:Completed)

It is working perfectly for the addition of new rows but it is calculating the percentage using the empty cells highlighted below, therefore giving me a lower completed percentage. Is there any way to solve this?

Thanks,

• ✭✭✭✭✭✭
Options

If you have an alternate cell that always has a response: ie a primary cell, you may be best to adjust the denominator to that column, as it will only return those values. Here is an example that I am using on one of my summary sheets.

=100*COUNTIF(Resolved:Resolved, 1) / COUNTM([Issue / Concern]:[Issue / Concern])

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

• ✭✭✭✭✭✭
Options

If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

• ✭✭✭
Options

Thanks again Colleen,

=1 * COUNTIF(Completed:Completed, 1) / COUNTM(Description:Description) fixed the issue for me 100* was giving me 1000% but entering 1* fixed my issue.

Thanks very much

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!