Count formula not working with helper column on first sheet, returning all zeros

This is the formula I have

=COUNTIFS({Overall Grade}, >=4.5, {Fleet}, "717", {Mod Helper}, "100")

On the main sheet, there is a helper column which converts the module number to a set 3 digit number (i.e. 102, 141 helper column will show 100)

No matter which return the helper column has, all return zero.

Find more posts tagged with

Formulas

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Jason F

I tested your formula using sample data as shown at the bottom, and I obtained the expected count.

Perhaps your range may not be correct, such as not referring to the proper column.

As shown above, if I change the {Mod Helper} range to the Primary Column range, I get zero.

https://app.smartsheet.com/b/publish?EQBCT=cb8bda9e85374332a6ef6317d55777f5

https://app.smartsheet.com/b/publish?EQBCT=52fd68e14bba408ebe65ccfe134e5618

kowal

hi @Jason F,

without the screenshot of data just based on formula it's difficult to guess what is wrong. Do you wanna past here some blurred screenshot?

Jason F

Here is some additional context.

On sheet A, we are collecting observations by row. Each row is a new submission. We are categorizing the module into 6 groups right now, based on ranges of 100. Formula is underneath. That formula will drive a single digit in the helper column.

On Sheet B, we are tabulating the data per Module range. If we take out the mod helper, the formula works fine and counts how many of a score (i.e. below would be a score of 4) are on the sheet for a fleet. Now we want to break it down by module group in addition. Adding the mod helper to the formula, plus the corresponding digit, returns a zero. We've tried just the mod helper in the formula, with nothing else and it still returns a zero even though there are plenty.

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.