Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Calculating percentage of check boxes checked in a column

    ✭✭✭
    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

    Sign in
    Try Smartsheet

    Best Answers

    • Community Champion
      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)

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

      Always happy to work through questions or concerns!

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓


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

      Always happy to work through questions or concerns!

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      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)

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

      Always happy to work through questions or concerns!

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Hi Colleen,

      That worked perfect, thank you!

      · Share on FacebookShare on Twitter
    • Community Champion

      Glad to help!

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

      Always happy to work through questions or concerns!

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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
    • Community Champion

      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.

      Always happy to work through questions or concerns!

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓


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

      Always happy to work through questions or concerns!

      · Share on FacebookShare on Twitter
    • ✭✭✭

      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

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions