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