% Complete for a row based on 6 columns of check boxes
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

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.
Let me know if this works!
Thanks,
Genevieve
Help Article Resources
Categories
Check out the Formula Handbook template!