Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Count Distinct is Clearly Incorrect.
I have a sheet with approximately 18,000 rows. One column is called "ID". I have a support column where I am trying to understand how many unique entries are in "ID" and I am using this formula:
=COUNT(DISTINCT(ID:ID))
It's returning a value of 1 (which is clearly not correct). The column is a text column. There are no special characters in the column at all.
Has anybody seen this? What am I missing?
Answers
-
Check to make sure that you don't have any #ERROR in the range you're calculating. I'm able to replicate your issue with it returning 1 with an #ERROR int eh range, but once I remove it, it counts up as it should.
-
Hi @Jason Tarpinian. I checked with CNTL-F for error and for # error and I also updated formula to =COUNT(DISTINCT(COLLECT([ID]:[ID], [ID]:[ID], AND(NOT(ISBLANK(@cell)), LEFT(@cell, 1) <> "#"))))
just in case that was tripping when there was a # at the beginning or there was a blank. No fix. Still showing as 1.
I don't seem to have any errors in that column. I can't figure out what is wrong with the count. Any other suggestions?
-
Try this one then, I was also able to replicate it when there was a number value in the range. Column3 shows the formulas that I have calculating in Column4. So maybe you have a number in your range of text fields. So the solution would be to find the distinct numbers and add them to the distinct texts:
=COUNT(DISTINCT(COLLECT(ID:ID, ID:ID, ISTEXT(@cell)))) + COUNT(DISTINCT(COLLECT(ID:ID, ID:ID, ISNUMBER(@cell))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!