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