Create Percentage Based off # of Checkboxes Checked

_Kay

complete checkboxes percentages.PNG

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

_Kay

@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!!

Jeff Reisman

@_Kay

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

_Kay

@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!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/354238#Comment_354238

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

_Kay

@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!!