Calculating percentage of check boxes checked in a column

John Kelly
John Kelly ✭✭✭
in Formulas and Functions

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.

· Share on FacebookShare on Twitter

Best Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    edited 02/07/23 Answer ✓

    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)

    Smartsheet Community Champion and Ambassador

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

    · Share on FacebookShare on Twitter
  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓


    Smartsheet Community Champion and Ambassador

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

    · Share on FacebookShare on Twitter

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    edited 02/07/23 Answer ✓

    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)

    Smartsheet Community Champion and Ambassador

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

    · Share on FacebookShare on Twitter
  • John Kelly
    John Kelly ✭✭✭

    Hi Colleen,

    That worked perfect, thank you!

    · Share on FacebookShare on Twitter
  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/361070#Comment_361070

    Glad to help!

    Smartsheet Community Champion and Ambassador

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

    · Share on FacebookShare on Twitter
  • John Kelly
    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?

    image.png


    Thanks,

    · Share on FacebookShare on Twitter
  • Colleen Patterson
    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])

    Smartsheet Community Champion and Ambassador

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

    · Share on FacebookShare on Twitter
  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓


    Smartsheet Community Champion and Ambassador

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

    · Share on FacebookShare on Twitter
  • John Kelly
    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!