% Complete for a row based on 6 columns of check boxes

Options
Judi Hancock
Judi Hancock ✭✭✭
edited 12/09/19 in Formulas and Functions

Hello - I have a task list that has 6 columns - each column has a check box. I am trying to determine a formula to calculate the % complete based on the populated check boxes. 

Ex: 6 Columns - if 3 columns were checked the % complete would be 50%, if 2 columns were checked then the % would be 33%, etc. 

can anyone help me?

thanks

Judi

Comments

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi Judi,

    One option of how to do this would be to add together 6 COUNTIF formulas that each count if their column has been checked for that row, then divide that whole formula by 6. For example, to count if Column1 has a checkmark, I would use this formula:

    =COUNTIF([Column1]@row:[Column1]@row, 1)

     

    Then, I would create this same formula for each column that has a checkbox, and I can add all these COUNTIFs together, put brackets around all of the COUTIFS, and use / 6 to divide by six, like so:

    =(COUNTIF([Column1]@row:[Column1]@row, 1) + COUNTIF([Column2]@row:[Column2]@row, 1) + COUNTIF([Column3]@row:[Column3]@row, 1) + COUNTIF([Column4]@row:[Column4]@row, 1) + COUNTIF([Column5]@row:[Column5]@row, 1) + COUNTIF([Column6]@row:[Column6]@row, 1)) / 6

     

    On my tester sheet, I have this formula within a column that has Percentage Format turned on so that it will automatically convert the percentage based on the decimal number that the formula returns.

    Counting Checkboxes

    Let me know if this works!

    Thanks,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!