The purpose of this article is to describe how to determine the number of unique items contained in a list in Microsoft Excel 97, Excel 2000, and Excel 2002.

Back to the top

For this article, "unique item" refers to an item that is unlike any others. It does not necessarily mean that the item occurs only one time in a list. For example, the number 1 is unique because it is different from anything else. The number 1 is a unique item, regardless of the number of times it may appear in a list.

Example 1:

In the list "1, 2, 3,", there are three unique items (1, 2, and 3). Each item in this list is different from the other items' and therefore each is unique.

Example 2:

In the list "1, 2, 3, 1, 2, 3," there are also three unique items (1, 2, and 3). The fact that the items are repeated in the list does not mean that the items are not unique; the items are just duplicated.

Example 3:

In the list "One, Two, Three, Four," there are four unique items (One, Two, Three, and Four).

Use one of the following methods to determine the number of unique items contained in a list.

Back to the top

Method 1: When Your List Does Not Contain Blank Entries

When your list in Excel does not contain any blank entries (empty cells), use the following array formula:

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

For example, create the following list in an Excel worksheet:

   Cell         Value
   ------------------
   A1             1
   A2             2
   A3             3
   A4             4
   A5             1
   A6             2
   A7             3
   A8             4
   A9             1
   A10            2

In cell A11, type the following array formula and then press CTRL+SHIFT+ENTER:

=SUM(1/COUNTIF(A1:A10,A1:A10))

The array formula should resolve to the number 4 because there are four unique items in this list (1, 2, 3, and 4).

Note If you receive #DIV/0! instead of the number 4 (in this example), there is at least one blank cell in the range contained in your array formula.

Back to the top

Method 2: When Your List Contains Blank Entries

When your list in Excel contains some blank entries (empty cells), use the following array formula:

=SUM(IF(LEN(A1:A10),1/COUNTIF(cellrange,cellrange)))

For example, create the following list in an Excel worksheet:

   Cell         Value
   ------------------
   A1             1
   A2             2
   A3             3
   A4                <-- Note: This is an empty cell
   A5             1
   A6             2
   A7             3
   A8             4
   A9             1
   A10            2

In cell A11, type the following array formula, and then press CTRL+SHIFT+ENTER:

=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

The array formula should resolve to the number 4 because there are four unique items in this list (1, 2, 3, and 4).

Back to the top