Count Unique values in a list - Microsoft Excel

To count unique values amongst a list of values, click here.

I am copy pasting the first suggestion here only to avoid future link failures.

Let’s say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains:

* The values 5, 6, 7, and 6, then the result is three unique values—5 , 6 and 7.
* The values “Buchanan”, “Dodsworth”, “Dodsworth”, “Dodsworth”, then the result is two unique values—”Buchanan” and “Dodsworth”

1. Ensure that the first row in the column has a column header.

2. On the Data menu, point to Filter, and then click Advanced Filter.

3. In the Advanced Filter dialog box, click Copy to another location.

4. If the range that you are counting is not already selected, delete any information in the List range box and then click the column (or select the range) that contains your data.

5. In the Copy to box, delete any information in the box or click in the box, and then click a blank column where you want to copy the unique values.

6. Select the Unique records only check box, and click OK.
The unique values from the selected range are copied to the new column.

7. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument. For example, if the range of unique values is B1:B45, then enter:
=ROWS(B1:B45)

This entry was posted on 27 Jun 2008 at 22:04 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.