How to distinct count in Excel

Distinct = all values that they appear once or many times
Unique= values that appear only once

Which do you need?

Hi Terressa,

Use the following formula:

Formula:

Please Login or Register  to view this content.

(You can edit the range yourself)

Then press CTRL + SHIFT + ENTER

Best,
Ernching

This will count the number of unique values (Alpha or Numeric) in a column and ignores blank cells.

Formula:

Please Login or Register  to view this content.

Enter with Ctrl + Shift + Enter (Array formula)

This will count unique numeric values:

Formula:

Please Login or Register  to view this content.

So will this:

Formula:

Please Login or Register  to view this content.

Even though it looks like negative numbers and 0 would not be counted, they are.

Assuming no empty cells within the data range.

On a range of 30,000 cells this is the most efficient formula (fastest to calculate).

Array entered**:

=SUM(IF(FREQUENCY(MATCH(A1:A30000,A1:A30000,0),ROW(A1:A30000)-ROW(A1)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Here is a small example:

Here is an Advanced Filter that is filtered on the name Dexter - Unique records.

	A	B	C	D	E	F	G
1	Name	Amount 1	Amount2		Name	Amount 1	Amount2
2	Roy	1	2		Dexter
3	Roy	1	2
4	Rex	2	3
5	Dexter	3	4		Name	Amount 1	Amount2
6	Dexter	3	4		Dexter	3	4
7	Dexter	5	6		Dexter	5	6

As you can see on the right, there are 3 records for Dexter. Records on row 5 and 7 are unique. Dexter 3 4 has two entries but only 1 is returned by the filter and Dexter 5 6 has only 1 record so it is returned by the filter. All duplicate records are ignored.

If you don't specify UNIQUE, the following is returned.

	A	B	C	D	E	F	G
1	Name	Amount 1	Amount2		Name	Amount 1	Amount2
2	Roy	1	2		Dexter
3	Roy	1	2
4	Rex	2	3
5	Dexter	3	4		Name	Amount 1	Amount2
6	Dexter	3	4		Dexter	3	4
7	Dexter	5	6		Dexter	3	4
8					Dexter	5	6

Here is an advanced filter without criteria other than Unique Records:

	A	B	C	D	E	F	G
1	Name	Amount 1	Amount2		Name	Amount 1	Amount2
2	Roy	1	2
3	Roy	1	2
4	Rex	2	3
5	Dexter	3	4		Name	Amount 1	Amount2
6	Dexter	3	4		Roy	1	2
7	Dexter	5	6		Rex	2	3
8					Dexter	3	4
9					Dexter	5	6

To get the UNIQUE values from the values in A1:A9

Formula:

Please Login or Register  to view this content.

Enter with Ctrl + Shift + Enter

To have Unique values from A1:A9 extracted in order small to large:

Formula:

Please Login or Register  to view this content.

Enter with Ctrl + Shift + Enter

To get a Distinct list from A1:A9 : (values appearing only once in the list)

Formula:

Please Login or Register  to view this content.

This is an unordered list which cannot be sorted unless the list is copied and then paste values is applied.

Originally Posted by newdoverman

To get the UNIQUE values from the values in A1:A9
To have Unique values from A1:A9 extracted in order small to large:
To get a Distinct list from A1:A9 : (values appearing only once in the list)
This is an unordered list which cannot be sorted unless the list is copied and then paste values is applied.

Thank you very much for answer

[edit]
I'm a little confused.
According to this definition,

from Pepe Le Mokko
Distinct = all values that they appear once or many times
Unique= values that appear only once

and your formula

To get a Distinct list from A1:A9 : (values appearing only once in the list)
Formula:
=IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($F$1:F1,$A$2:$A$9)+(COUNTIF($A$2:$A$9,$A$2:$A$9)<>1),0,0),0)),"")

it confuses me, I want to resolve this dilemma

Unique values are those values that appear only once in a list after all duplicate values are removed. That means if 5 is in a list 5 times it will be counted once as a Unique value. Distinct values only appear in a list 1 time and have no duplicates.