COUNTIF Formula Returns Zero in Sheet Summary

I have a very simple COUNTIF formula that isn't working in the sheet summary section. For example, I'm trying to count how many times the year "2017" appears in a column called "Tablet Year". The formula should be:

=COUNTIF([Tablet Year]:[Tablet Year], 2017)

I've also tried surrounding the "2017" in quotation marks in case it's being recognized as text rather than a number, but it still returns 0. Meanwhile, I can see 2017 appearing multiple times in that column, along with other years and blank values.

I should also mention the values I'm trying to count are being pulled into this sheet through an Index/Match column formula. Maybe I'm wrong, but I don't believe that should affect the results of a simple COUNTIF.

I also tried the formula in an individual cell and it's still not working. There are no extra spaces after the 4 digits of each year, so it should be an exact match with the criteria I'm using in the formula.

Any ideas?

Thanks!

Find more posts tagged with

Sheets

Formulas

Sheet Summary

columns

Countif

Accepted answers

Kelly Moore

Hey @jordan.navarro23

Let try some troubleshooting to figure out what's going on in your sheet.

Try this: =COUNTIF([Tablet Year]:[Tablet Year], FIND(2017, @cell) > 0) What did this do?
Wrap your INDEX/MATCH formula in a VALUE function. What did this do?

Kelly

All comments

Kelly Moore

Hey @jordan.navarro23

Let try some troubleshooting to figure out what's going on in your sheet.

Try this: =COUNTIF([Tablet Year]:[Tablet Year], FIND(2017, @cell) > 0) What did this do?
Wrap your INDEX/MATCH formula in a VALUE function. What did this do?

Kelly

jordan.navarro23

https://community.smartsheet.com/discussion/comment/466904#Comment_466904

This one did the trick, thank you Kelly!

=COUNTIF([Tablet Year]:[Tablet Year], FIND(2017, @cell) > 0)

Any idea why the original formula below wouldn't provide the same result?

=COUNTIF([Tablet Year]:[Tablet Year], 2017)

Kelly Moore

Hey @jordan.navarro23

I suspected you had a leading space or apostrophe in your cells. Without seeing a screenshot of an opened cell, I can't know for sure.

I'm glad we found something that works- in my opinion it never has to be pretty, it just has to work.

Kelly

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.