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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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
Check out the Formula Handbook template!