Count but ignore duplicate data

  • Thread starter Thread starter Rechie
  • Start date Start date
R

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,
 
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.
 
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.
 
Back
Top