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,

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,

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,

Thanks Guys,

It is working well.

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.