Microsoft Office Tutorials and References

In Depth Information

Figure 7-5:
Using an array formula to count the number of unique values in a range.

You can access the workbook count unique.xlsx shown in Figure 7-5 at this book's

website.

The array in range C1:C12 consists of the count of each value in
Data.
For example, the number 125 appears

three times, so each array element that corresponds to a value of 125 in the
Data
range has a value of 3.

Range D1:D12 contains the following array formula:

{=1/C1:C12}

This array consists of each value in the array in range C1:C12, divided into 1. For example, each cell in the ori-

ginal
Data
range that contains a 200 has a value of 0.5 in the corresponding cell in D1:D12.

Summing the range D1:D12 gives the number of unique items in
Data.
The array formula presented at the be-

ginning of this section essentially creates the array that occupies D1:D12 and sums the values.