How to determine the number of unique items in a list in versions 97, 2000, and 2002 of Excel

Article translations Article translations
Article ID: 823573 - View products that this article applies to.
Expand all | Collapse all

On This Page

SUMMARY

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.

MORE INFORMATION

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.

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.

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).

Properties

Article ID: 823573 - Last Review: January 16, 2007 - Revision: 4.3
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel 2008 for Mac
Keywords: 
kbfunctions kbinfo kbhowto KB823573

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com