Microsoft KB Archive/823573

= How to determine the number of unique items in a list in Excel 2007 and in Excel 2003 =

Article ID: 823573

Article Last Modified on 1/16/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003

-



SUMMARY
The purpose of this article is to describe how to determine the number of unique items contained in a list in Microsoft Office Excel 2007 and in Microsoft Office Excel 2003.



MORE INFORMATION
For this article, &quot;unique item&quot; 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 &quot;1, 2, 3,&quot;, 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 &quot;1, 2, 3, 1, 2, 3,&quot; 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 &quot;One, Two, Three, Four,&quot; 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).



Keywords: kbfunctions kbinfo kbhowto KB823573

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.