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 fruits. But just how many unique types of fruit do I have in my list?
Firstly Ensure the first row of your column contains a column name. Mine is Name Of Fruit.
- 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:F11)
Go ahead and give this a try……
Other Excel Tips and Tricks You Might Like.
1. Working With Advanced Filtering In Excel