Article ID: 823573 - Last Review: January 16, 2007 - Revision: 4.3 How to determine the number of unique items in a list in versions 97, 2000, and 2002 of ExcelOn This PageSUMMARYThe 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 INFORMATIONFor 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 EntriesWhen your list in Excel does not contain any blank entries (empty cells), use the following array formula:Cell Value ------------------ A1 1 A2 2 A3 3 A4 4 A5 1 A6 2 A7 3 A8 4 A9 1 A10 2 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 EntriesWhen your list in Excel contains some blank entries (empty cells), use the following array formula: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 | Article Translations
|
| United States | Change | | | All Microsoft Sites |
Back to the top
|
