Create Percentage Based off # of Checkboxes Checked
1) Creating a percentage to tally how much out of each section is checked completed.
2) Then also need to setup a formula to do the same for the overall sheet including all subheaders.
This is the formula I have so far for the individual subheader sections. But I keep getting Invalid Operation errors. Not sure what I'm missing here. Screenshot attached for further context.
=COUNTIF(Done3:Done8, 1) / (COUNTIFS(Done3:Done8, 0, NOT(ISBLANK(@cell) + (COUNTIF(Done3:Done8, 1)))))
Best Answer
-
@Jeff Reisman FINALLY. Sorry had been screwing with this for waaay too long now. Managed to get it though. Here's the final working formula:
=ROUND(COUNTIF(Done3:Done8, 1) / (COUNTIFS(Done3:Done8, 0) + COUNTIFS(Done3:Done8, 1)), 2) * 100 + "%"
Had to add in the rounding to allow it to display inside the checkbox column.
Appreciate your help Jeff!!
Answers
-
See all those parentheses stacked up at the end? I think you need to distribute some of them back to the left:
=COUNTIF(Done3:Done8, 1) / (COUNTIFS(Done3:Done8, 0, NOT(ISBLANK(@cell))) + COUNTIF(Done3:Done8, 1))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman Thank you! I adjusted the parentheses to every different setup possible and it's still throwing Invalid Operation. :(
Any other ideas?
Thank you!
-
Troubleshooting:
Try each of your functions within your formula separately, making sure that each one returns a numeric value
I'm guessing that the COUNTIFS might be the problem - you need to list the range a second time, because each criterion needs a criterion range, even if it's the same range. Syntax: COUNTIFS(range1, criterion1, range2, criterion2):
COUNTIFS(Done3:Done8, 0, Done3:Done8, NOT(ISBLANK(@cell)))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman FINALLY. Sorry had been screwing with this for waaay too long now. Managed to get it though. Here's the final working formula:
=ROUND(COUNTIF(Done3:Done8, 1) / (COUNTIFS(Done3:Done8, 0) + COUNTIFS(Done3:Done8, 1)), 2) * 100 + "%"
Had to add in the rounding to allow it to display inside the checkbox column.
Appreciate your help Jeff!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
