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:


Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.