Creating a percentage from multiple tick boxes
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:
Best Answers

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 + "%"

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

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 + "%"
Answers

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

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

Try something like this...
=COUNTIFS([Column Name]:[Column Name], 1) / COUNTIFS([Column Name]:[Column Name], OR(@cell = 1, @cell <> 1))

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

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 + "%"

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?

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

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

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

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?

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

Thank You. I just needed to convert to a % at the end of the formula.
Really appreciate your help on this.

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 + "%"
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!