MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Count but ignore duplicate data

• Follow

```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

```
 0

```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
>
```
 1

```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
>
```
 0

```Thanks Guys,

It is working well.

```
 0

```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.
>
```
 0

4 Replies
1444 Views

Similiar Articles:

7/26/2012 9:33:15 PM