I'm looking to set up a formula that will show % completed based on checkboxes
I'm looking to set up a formula that will show % based on checked/unchecked boxes. For example, in 1 column, I have 1 checked box, and 1 unchecked box. I am looking for it to say 50% are checked. The difficulty lies in the fact that it's information being pulled from another sheet, and it needs to be set within a certain date range
Best Answer
-
The function you're showing takes the average of the count based on a criteria. You still aren't dividing it by the total amount to get a percentage.
Sorry, but if you count a total of 1 based on the criteria involved (checked cells) and take the average, you're calculating 1 over a total of 1 (because there's only 1 value checked) which equals 1.
What are you using to calculate the total amount?
Answers
-
Hello @dhuff11
You could try using the COUNTIFS() function to enter multiple criteria such as whether its checked and within a certain time frame.
Then use the COUNT() function to get a count on the entire range of values.
Then divide the result by the total and get the percentage.
-
The issue I face with that formula is the scale of what it's going to be used for. This formula will be used across multiple cells and will be adjusted to each cell. I would end up with a massive amount of columns for each main column I'm trying to get a percentage from. Is there a way for it to be done all in 1 formula? The formula I have currently is:
=AVG(COUNTIFS({Work Center}, "Department", {HRxHR}, 1, {Date}, AND(@cell > DATE(2023, 7, 15), @cell < DATE(2023, 7, 23)), {Shift}, "First"))
HRxHR is the column with the checkboxes I'm trying to get the percentage of. This formula was able to return the correct quantity of checked boxes, but not the percentage compared to unchecked. Instead of showing 1, it should show 50%.
-
The function you're showing takes the average of the count based on a criteria. You still aren't dividing it by the total amount to get a percentage.
Sorry, but if you count a total of 1 based on the criteria involved (checked cells) and take the average, you're calculating 1 over a total of 1 (because there's only 1 value checked) which equals 1.
What are you using to calculate the total amount?
-
I actually needed this for a sheet.
Instead of checkboxes we have the symbol (counting "blue"). But this would work for checkboxes also.
I added a column to sum how many were checked over a series of columns.
=COUNTIFS([BG PDF / Ver]@row:[AHA Status / Ver]@row, "blue")
Then used that sum to calculate the % complete.
Hope this helps.
-
Thank you all, this has been solved. I ended up having to create some helper columns and it helped tremendously.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!