Percentage formula based on a checkbox.

Any suggestions on how to make this exceptions formula more accurate? Smartsheets keeps adding 10 rows to my sheet with no data causing my formula to be off.

I have 88 rows with data and a total of 98 rows. Out of the 88 row 21 are an exception with the checkbox selected.

This is the formula I am using.

=COUNTIF({Master Sheet Exceptions}, 1) / COUNT({Master Sheet Exceptions})

The answer should be 23% (21*100)/88=23.86 but Smartsheet is giving me a 21% (21/100)/98=21.43.

Find more posts tagged with

Formulas

Accepted answers

All comments

Lucas Rayala

That's annoying. Is there another column you could reference that's always populated, so you can change the "COUNTIF" to a "COUNTIFS" and use that other column as another criteria?

I.e. = COUNTIFS({Master Sheet Exceptions}, {Master Sheet Exceptions}, 1, {Non-blank reference column}, <>"")/ COUNTIFS({Master Sheet Exceptions},{Non-blank reference column}, <>"")

If you have a Row ID column, I don't think the row ID would populate unless you added other data to the row--you could potentially use that as your non-blank reference.

AlexisJ

@Lucas Rayala That is a great idea. I will tinker with the other rows to see what other criteria I can build into it.

Lucas Rayala

@AlexisJ glad to help!

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.