How to Count Unique Values in Excel

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.

count unique data

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

select range cells

Advanced Filter

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

data advanced ribbon

Click yes

count unique click yes

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

count unique advanced filter

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:

count unique 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.

count unique sumproduct formula

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

Sum Countif array formula to count unique values

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.

See also  How to Apply a Formula to an Entire Column in Excel