It is quite easy for you to get unique value where there are so many duplicates, especially if you would like to paste them in a new location. We are going to count unique values together.

Have a data with duplicates.

Select the range of cells, or have it all in a table.

## Advanced Filter

Click on Data Tab (1), and choose Advanced (2) in Sort & Filter.

Click yes

Click copy to another location (1), where you want to copy it to (2), check on the unique records only (3).

List Range: **=&A&1:&A&14**

Note: If the cells you choose in step 2 aren’t large enough to contain all the unique numbers, Excel will automatically enlarge them.

Unique Values:

## Sumproduct Formula

Alternative solution would be to use Sumproduct function combined with Countif function.

The formula to copy paste is: **=SUMPRODUCT(1/COUNTIF(A2:A14,A2:A14))** where A2:A14 is the range of data set.

## Sum Formula

Another method to count unique values is a Sum function combined with Countif function again.

The formula is as follows: **{=SUM(1/COUNTIF(A2:A14,A2:A14))}**

For sure you have noticed curly braces. The formula is an array formula so to enter that you need to use CTRL + SHIFT + Enter buttons.

As you can see there is a possible that data in your tables or reports will be duplicated. To extract only distinct/unique data like products/employee/companies/cities etc. just use this trick.

You can also count distinct values in pivot table.