Count but ignore duplicate data

[Rechie]

Hi Expert, I need your help on how to count data but to ignore duplicates.

Below count results should be = 3 (ignore duplicates)
Inv No.
9123
9123
9125
9128
9128

Thanks for your support,

 

[Jacob Skaria]

Try
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

 

[Mike H]

Hi,

One way

=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

Note that Bernd P has done an analysis of different methods of doing this
dependent on dataset size. look here.


http://www.sulprobil.com/html/count_unique.html
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rechie]

Thanks Guys,

It is working well.

 

[Mike H]

Glad to help but don't forget to check out the link I gave you because the
sumproduct method becomes very slow for larger datasets and the website has
other better methods
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.