Extract and count unique items in a list

Excel easily allows to extract a list of unique items and count them. Handy when you have to extract an order list from a BOM of a PCB for example 😉

When you have a list of items and you want to list and count the unique entries, follow these steps:

  • Select ‘Data’ -> ‘Filter’ -> ‘Advanced filter’
  • Select ‘Action’ -> ‘Copy to another location’
  • Fill in ‘List Range’ to be the column containing the data
  • Fill in ‘Copy to’ to be the target colums
  • Enable the ‘unique records only’ checkbox
  • Click OK

Then, to count the number of unique entries, do as follows:

  • Next to the target colum, enter the following formula: =COUNTIF(F:F,I3). This formula counts the number of times the text in cell I3 is encountered in the source list range.
  • Expand the formula to the complete target column height.

And you’re done!