To Count unique string values among duplicates in Excel select the range of cells and Go to Data tab and click Advanced in the Sort & Filter.
Select “Copy to another location”. Make sure the List range is correct.
Select the cell for “Copy to:”
Check the “Unique records only”
Press Ok. In a new cell insert and use the “ROWS” function where the range is the newly created “Copy to:” list
=ROWS(G1:G24)
The unique count is displayed.
Alternativ
To count unique values among duplicates in Excel, you can use the combination of the “COUNTIF” and “SUMPRODUCT” functions. Here’s how you can do it:
Assuming you have a list of values in column A, starting from A1, and you want to count the unique values:
- In an empty cell, enter the following formula:
=SUMPRODUCT(1/COUNTIF(A1:A10;A1:A10))
Note: Replace A1:A10 with the actual range that contains your values.
- Press Enter to get the count of unique values. The formula divides 1 by the count of each value using the COUNTIF function. Then, the SUMPRODUCT function sums up the reciprocal values, resulting in the count of unique values.
Example: If your values are in cells A1 to A10, the formula would look like this:
=SUMPRODUCT(1/COUNTIF(A1:A10;A1:A10))
Remember to adjust the range based on the actual location of your values.
This formula will return the count of unique values in the range A1:A10.