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.
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.
Leave a Reply