Countif not working for duplicate check in column
Has anyone been having issues with the countif formula to check for duplicates in a column? My output keeps showing zero even though there are 2 of the same numbers in one column.
Best Answer
-
I have seen this issue when there are leading zeros. Try using an @cell reference.
=COUNTIF([Column Name]:[Column Name], @cell = [Column Name]@row)
Answers
-
I have seen this issue when there are leading zeros. Try using an @cell reference.
=COUNTIF([Column Name]:[Column Name], @cell = [Column Name]@row)
-
That is really weird! Any idea of the underlying cause of that? A bug?
There is nothing in the documentation for @cell to suggest it's use here, yet I just replicated Warren's issue and your fix. It's not just numbers with leading zeros, it's any number stored as text - but text/number combinations work fine.
Ex. Without the @cell, the formula fails with '0420 and '5555, but not with 00Jeff.
All the red cells contain: =COUNTIF(TypeB:TypeB, TypeB@row). The yellow cells use @cell = TypeB@row.
Here's a weirder part: The failed formula doesn't always show a value of 0! I added the two numeric 8888 cells at the bottom, got the correct count of 2. Then I added the formula to the bottom row and entered '8888, and it gave the cell a count of 2 (even though there are 3 cells with '8888 in the column.)
Then I entered the formula on the next line down, and entered '5555, and I got 4, just like I did for the numeric 5555 above. I entered another '5555 and another '8888, but neither count updated.
BUT THEN... I added another numeric 5555 and another numeric 8888, and guess what? The counts updated for '5555 and '8888! < What??? >
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Now you are mixing data types. You have text and numerical. You would need to convert it all to text (so you can keep leading zeros and whatnot) and then your COUNTIFS should be working.
-
I know why they shouldn't match. The weird thing is that it IS matching across data types - but only in one direction! The formula trying to match a number stored as text doesn't count the matching number stored as text, but it does match the number stored as number. But vice versa, matching a number stored as number it ignores the same number stored as text.
(I don't have a particular issue to solve, I'm just trying to map out the rules here and see if I can understand why adding "@cell =" makes the formula able to match number stored as text to number stored as text.)
To recap, the formula =COUNTIF(TypeB:TypeB, TypeB@row)
When TypeB@row is '8888, the formula ignores '8888, and counts 8888, which is backwards.
When TypeB@row is 8888, the formula counts 8888 and ignores '8888, as it should.
When TypeB@row is 001ABC, the formula counts 001ABC, as it should.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff Reisman I haven't really tested it much personally as I always just go with converting everything to text unless I know for sure everything will be numbers.
Have you checked to see which order the data is in? If text is the first value in the column vs if number is the first value in the column?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
