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
Alternatively, you can count the number of unique values using a formula.
The formula =SUMPRODUCT(1/COUNTIF(A2:A14,A2:A14)) uses the Countif function to count the occurrences of each value within the range A2:A14.
By taking the reciprocal (1 divided by the count) and summing these reciprocals using Sumproduct, you effectively count each unique value once.
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))}
Note that the curly braces {} are not typed manually; they are automatically added by Excel when you press Ctrl + Shift + Enter to enter the array formula.
These formulas count the number of distinct values in the range, not extract the unique values themselves. To actually extract the unique values, the Advanced Filter method is more suitable.
While these formulas provide a count of unique values, PivotTables also offer a convenient way to both count and display unique items within a dataset.
Leave a Reply