count unique values in Excel

Try using mapflo for data analysis instead of Excel. Get started for free.

Summary

You would like to know how many unique values there are in a range. You can use a formula that incorporates the SUM, IF, LEN, MATCH and FREQUENCY functions or use advanced filter (description below)

How to use advanced filter to count unique values

Click on Data > Advanced to bring up the Advanced Filter pop-up.

• Select Copy to another location.
• Select the range you want to find unique values in ‘List range’.
• Add the cell you want the results populated in in ‘Copy to’
• Select ‘Uniqur records only’
• Click OK

In the example above this populates only unique rows from the list range in cell E1 onwards.

You can then use the formula =ROWS(E2:E9) to count the number of rows in the copied range and therfore the number of unique values.

Using mapflo to find Unique values

If you like Excel then you’ll love mapflo.

It’s easy to find unique values in a dataset using the Rank & Partition Node.

Add a Rank & Partition Node to the dataset. Select Function ‘Index’ and Partition by the columns you want to check for uniqueness across.

In the example below we are looking for unique rows for Item, Size and Qty.

The data table highlights any duplicate row with an Index of 2 (as it is the second occurence in the partition of Item, Size and Qty).

Now Add a filter node and filter by Index = 1. The top section of the Preview Pane shows how many rows in the dataset.

If you want to see how many duplicates there are then filter by Index > 1.

If you want to find unique rows for Item and Size only then only partition by those two columns. There are three rows with the same Item (Sandals) and Size (M):

Get started with mapflo for free.