Creating a percentage from multiple tick boxes

Karl Twist

Hi All,

This may be simple but i'm struggling to get an output so hoping you can help...

I'm trying to calculate a percentage based on the number of completed tick boxes in a row/column.

I'd be ok if this initially does a count in one cell and then coverts to a percentage in another. This is the example data below:

Paul Newcome

My apologies. I got ahead of myself there.

=(COUNTIFS([Column Name]:[Column Name], 1) / COUNTIFS([Column Name]:[Column Name], OR(@cell = 1, @cell <> 1))) * 100 + "%"

Paul Newcome

It would look something like this...

=COUNTIFS([1st Column Name]@row:[Last Column Name]@row, 1) / COUNTIFS([1st Column Name]@row:[Last Column Name]@row, OR(@cell = 1, @cell <> 1))

Paul Newcome

My answer at the top of this thread should help with that.

Wrap the COUNTIFS/COUNTIFS in parenthesis.

=(COUNTIFS(.........) / COUNTIFS(.............))

Multiply by 100

=(COUNTIFS(.........) / COUNTIFS(.............)) * 100

And then add the % symbol

=(COUNTIFS(.........) / COUNTIFS(.............)) * 100 + "%"

Paul Newcome

Where are you wanting to put the calculation and are you wanting to cover the entire column or just a certain number of rows?

Karl Twist

Hi Paul

Calculation would be in the highlighted box but would cover all rows. Also looking to do a similar calc across multiple columns

Paul Newcome

Try something like this...

=COUNTIFS([Column Name]:[Column Name], 1) / COUNTIFS([Column Name]:[Column Name], OR(@cell = 1, @cell <> 1))

Karl Twist

I get a Boolean Expected error when trying that...

Paul Newcome

My apologies. I got ahead of myself there.

=(COUNTIFS([Column Name]:[Column Name], 1) / COUNTIFS([Column Name]:[Column Name], OR(@cell = 1, @cell <> 1))) * 100 + "%"

Karl Twist

Perfect. Thank you.

This gives me the % remaining. How would I go about calculating the % completed. Would this be easier in the same formula or in a separate cell?

Paul Newcome

It depends on how exactly you wanted to have it displayed.

Karl Twist

I'm easy on the display, basically just looking to flip the result to show % complete

Paul Newcome

Actually the above SHOULD show the % Completed since it is counting checked boxes.

Karl Twist

Apologies, my cell range wasn't correct. That's now covered.

One final question...

How would the formula be amended to cover data across columns rather than rows?

Paul Newcome

It would look something like this...

=COUNTIFS([1st Column Name]@row:[Last Column Name]@row, 1) / COUNTIFS([1st Column Name]@row:[Last Column Name]@row, OR(@cell = 1, @cell <> 1))

Karl Twist

Thank You. I just needed to convert to a % at the end of the formula.

Really appreciate your help on this.

Paul Newcome

My answer at the top of this thread should help with that.

Wrap the COUNTIFS/COUNTIFS in parenthesis.

=(COUNTIFS(.........) / COUNTIFS(.............))

Multiply by 100

=(COUNTIFS(.........) / COUNTIFS(.............)) * 100

And then add the % symbol

=(COUNTIFS(.........) / COUNTIFS(.............)) * 100 + "%"