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

Judi Hancock

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

Genevieve P.

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