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.

Best Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    edited 02/07/23 Answer ✓
    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.

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓
    Options


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

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    edited 02/07/23 Answer ✓
    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.

  • John Kelly
    John Kelly ✭✭✭
    Options

    Hi Colleen,

    That worked perfect, thank you!

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

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

  • John Kelly
    John Kelly ✭✭✭
    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,

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    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.

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓
    Options


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

  • John Kelly
    John Kelly ✭✭✭
    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!