Count but ignore duplicate data
 

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,

Count but ignore duplicate data
 

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

--
Jacob (MVP - Excel)


"Rechie" wrote:

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,

Count but ignore duplicate data
 

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" wrote:

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,

Count but ignore duplicate data
 

Thanks Guys,

It is working well.

Count but ignore duplicate data
 

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.


"Rechie" wrote:


Thanks Guys,

It is working well.