Q: Create a Function =DISTINCT() This thread is locked from future replies

I deal with long lists of numbers that I need to count distinct values from. Some are numbers only, other text and numbers. 

A function =DISTINCT() where () in a column, row or array would simply return the amount of unique values in that data set. 

Currently, there is no way to do this simply. 

Thank you 




Non-array versions (Enter only)

in A13:

=SUMPRODUCT( (A2:A11<>"") / COUNTIF(A2:A11;A2:A11&""))

in A14:

=SUMPRODUCT((A2:A11 <> "") * (COUNTIF(A2:A11; A2:A11&"")=1))

Same comment as Bernie: strive not to reference the full column (i.e. A:A) for perf. reasons.

An Excel UserVoice system exists to suggest improvments. There are already a few topics re. COUNT UNIQUE/DISTINCT (feel free to vote) and one is already Planned

Cheers | Lz.

Did this solve your problem?

Sorry this didn't help.

Question Info

Views: 19 Last updated: February 22, 2018 Applies to: