Excel Factor 9 Count Unique Items in a List

This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee.

There are times when you wish to determine how many unique entries are in a list.  Wouldn’t it be great to be able to select a range of cells, like the one shown below, and have a function return, in this case, the number 9?

Unfortunately, there is no single function in Excel that will determine this number.  We can, however, use a combination of MATCH and FREQUENCY functions nested with IF and SUM to produce the desired result.

FruitList would yield 9

Count number of unique items in a column of text

(with no blank cells)

The below formula will return the answer, provided the data has no blank cells interspersed with the data.

Non-Array Solution

=SUM(IF(FREQUENCY(MATCH(FruitList,FruitList,0),MATCH(FruitList, FruitList,0))>0,1))

Note: 'FruitList' is a named range for cells A1:A25.

If you’re feeling adventurous, you can accomplish the same result with an array formula.  Remember to use the CTRL and SHIFT key when you enter the formula so the system knows you are working with an array.

For information on arrays and their purpose, see the Array Functions in Excel tutorial.

Array Solution

(requires CTRL-SHIFT-ENTER)

=SUM(1/COUNTIF(FruitList,FruitList))

Count number of unique items in a column of text

(with blank cells)

If you find yourself in a situation where blank cells are mixed within your data, the following two examples will accommodate the blank cells.

Non-Array Solution

=SUMPRODUCT((FruitList<>"")*(1/COUNTIF(FruitList,FruitList &"")))

Array Solution

(requires CTRL-SHIFT-ENTER)

=SUM(IF(COUNTIF(FruitList,FruitList)=0,"",1/COUNTIF(FruitList,FruitList)))

When dealing with a PivotTable

If you need to know how many unique items there are in relation to another variable, e.g. Permits per Business Unit Operation, a PivotTable is a perfect tool for calculating those answers quickly.

Unfortunately (there’s that word again), the counting/summing tools built into the PivotTable are not able to distinguish between counting all items or one of each sub-category; it counts all instances of an entry.

In the example below, the PivotTable would return 8 for BRN-Dante and 15 for BRN-Green Valley.  We simply want to know how many permits were issued per BU-Operation.

In other words, 2 permits for BRN – Dante and 6 permits for BRN – Green Valley.

Create a new column in the data and add the following formula next to the first record (cell C2):

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)

This will flag the first permit in each permit number group with 1 and all repeated permits with 0.

The column can then be added to a PivotTable with the SUM function as the operator, counting the number of unique permits per BU-Operation:

Thanks again, Bryon for sharing your knowledge.

Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world.  His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.

Bryon says “Excel is by far my favorite among all the Office applications due to the almost infinite number of situations it can be used.  There literally seems to be no end to its usefulness.

My favorite Excel tools are difficult to narrow down.  Without getting into third party add-ins, I would have to say PivotTables (especially with the addition of Slicers.  WOW!  Are those things awesome!!), the Text to Columns tool (what a time saver), and Macros.”

Vote for Bryon

If you’d like to vote for Bryon’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂