Microsoft Office Tutorials and References
In Depth Information
Advanced Counting Formulas
The following array formula is a modified version that is not case sensitive:
Counting the number of unique values
The following array formula returns the number of unique values in a range named Data:
To understand how this formula works, you need a basic understanding of array formulas. (See
Chapter 14 for an introduction to this topic.) In Figure 7-5, range A1:A12 is named Data. Range
C1:C12 contains the following multicell array formula. A single formula was entered into all 12 cells
in the range.
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 on the
The array in range C1:C12 consists of the count of each value in Data. For example, the number
100 appears three times, so each array element that corresponds to a value of 100 in the Data
range has a value of 3.