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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.