# Thread: count no duplicate value (2000)

1. ## count no duplicate value (2000)

i have in the column A5:A100 this value:
1
1
1
2
2
2
3
3
3
4
4
4
5
5
5
6
6
6
9
9
9
52
52
23
23
23
23
...

my problem is to count (with a formula) the value not duplicate and insert the result in D2
In this case D2=9

2. ## Re: count no duplicate value (2000)

Provided there are no blanks and no text cells:

=SUM(N(FREQUENCY(A1:A100,A1:A100)>0))

See Chip Persons' site Duplicates and Unique Values page for more info.

3. ## Re: count no duplicate value (2000)

You can use an array formula, with your range in A5:A100, you need to enter the formula by using Control+Shift+Enter
There must be no blanks for this to work.

=SUM(1/COUNTIF(A5:A100,A5:A100))

=SOMMA(1/CONTA.SE(A5:A100;A5:A100))

4. ## Re: count no duplicate value (2000)

http://longre.free.fr/english/index.html

=COUNTDIFF(RANGE)

5. ## Re: count no duplicate value (2000)

This will handle blanks, either by omission and/or formula generated blanks,

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•