Tom’s Tutorials For Excel: List and Count Unique Items

Tom’s Tutorials For Excel: List and Count Unique Items

Instead of creating a pivot table, you can use formulas to list and count unique items from a larger list. In the following pictures, a clothing store’s daily record of items sold is condensed into a list of unique items in column C, and a count of those unique items in column D.

In Picture #1, the array formula
=INDEX($A$2:$A$25,MATCH(0,COUNTIF($C$1:C1,$A$2:$A$25),0))
is placed in cell C2.
Recall, array formulas are applied to a cell by pressing Ctrl+Shift+Enter, not just Enter.
The curly braces are entered automatically by Excel; you do not enter them yourself.

Picture #1

In Picture #2, the formula =COUNTIF($A$1:$A$25,C2) is entered into cell D2.

Picture #2

Picture #3 shows the result after both formulas were copied down until all unique items were listed. You will know you reached that point when the copied array formula returns an error.
Picture #3