How To Count Unique Items In A Range Of Cells 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 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

2. Understand And Debug ExcelFormula Errors

3. Create A Timestamp In Excel

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts