Calculating percentage of check boxes checked in a column

John Kelly

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.

Colleen Patterson

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)

Colleen Patterson

Colleen Patterson

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)

John Kelly

Hi Colleen,

That worked perfect, thank you!

Colleen Patterson

https://community.smartsheet.com/discussion/comment/361070#Comment_361070

Glad to help!

John Kelly

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,

Colleen Patterson

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])

Colleen Patterson

John Kelly

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