Count unique items using Advanced Filtering

Whilst using Excel there will be a good number of scenarios where you want to find the number of unique values you have in a range of cells. this could be Customer Names, Invoice Numbers, Order Numbers or product codes. There a a few great ways to do this in Excel, today I want to share with you how to count unique items using the Advanced Filter Function in Excel.

Let’s as always look at an example for clarity.In my example I have a list of names. But just how many unique types of names do I have in my list?

Firstly Ensure the first row of your column contains a column name. Mine is Name Of People.

Data Tab

Sort and Filter Group

Filter- Advanced Filter

Select Copy To Another Location (if the range you are counting is not already selected then delete any prior information), in the List Range then select your data list

Select Copy To (delete any prior information) and click the area on the worksheet where you want the unique values to be copied to

Hit Ok your unique values will be copied

To count your items click in the first blank cell underneath your unique list and enter the ROWS formula as below- replacing your range of cells where appropriate. The formula for my example is =ROWS(F2:F7). The example is shown below:

Comments