COUNTIF formula where range is a formula column

I'm trying to create a COUNTIF formula in a Sheet Summary field where the range is a Text/number column that contains a column formula. I keep getting an INVALID DATA TYPE error no matter what I do. The Sheet Summary field is also defined as a Text/number field. What am I doing wrong? Note, I get the same error when I try to use the formula in a sheet cell.

=COUNTIF(Year:Year, 2025) or

=COUNTIF(Year:Year, "2025")

Find more posts tagged with

Accepted answers

Jgorsich

#Invalid Data Type is a weird error to trigger from that formula, it seems most likely that it is being passed through instead. Check you [Year] column - if ANY rows are resulting in that same error message, this will propagate through all ensuing formulas referencing the range. Add some error handling in THAT column and your formula might start working.

All comments

Jgorsich

Michael Chohrach

@Jgorsich UGH! Why didn't I catch that? That's exactly what the problem was. Thanks!

Jgorsich

:) Glad I could help! I've missed that a few times as well - it would help if there was a unique error message (like "#Error in Range") instead of passing through the original message. Going with the pass through choice makes it WAY more confusing to troubleshoot.

Jgorsich

I actually just submitted this on the request page as a result of this question:

New Formula Error message #ERROR IN RANGE - Smartsheet Community

That pass-through of the error messages is super annoying. Cheers!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.