Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

The following array formula is a modified version that is not case sensitive:

{=(SUM(LEN(Data))-SUM(LEN(SUBSTITUTE(UPPER(Data),

UPPER(Text),””))))/LEN(Text)}

Counting the number of unique values

The following array formula returns the number of unique values in a range named
Data:

{=SUM(1/COUNTIF(Data,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.

{=COUNTIF(Data,Data)}

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

companion CD-ROM.

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.