COUNTIF Formula Returns Zero in Sheet Summary

jordan.navarro23

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!

Kelly Moore

Hey @jordan.navarro23

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

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

Kelly

Kelly Moore

Hey @jordan.navarro23

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

1. Try this: =COUNTIF([Tablet Year]:[Tablet Year], FIND(2017, @cell) > 0) What did this do?
2. 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