Count Distinct is Clearly Incorrect.

A.J.

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?

Jason Tarpinian

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.

A.J.

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?

Jason Tarpinian

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))))