count no duplicate value (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

With this add-in,

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

=COUNTDIFF(RANGE)

Reply | Quote

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

=SUMPRODUCT((A1:A100″”)/COUNTIF(A1:A100,A1:A100&””))

Reply | Quote

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

=SUMPRODUCT((A1:A100″”)/COUNTIF(A1:A100,A1:A100&””))

Reply | Quote

With this add-in,

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

=COUNTDIFF(RANGE)

Reply | Quote