Microsoft Office Tutorials and References

In Depth Information

**Advanced Counting Formulas**

This next array formula operates like the MODE function except that it works with both text and

values:

{=INDEX(Data,MATCH(MAX(COUNTIF(Data,Data)),COUNTIF(Data,Data),0))}

If there is more than one most frequent value, the preceding formula returns only the

first in the list.

Counting the occurrences of specific text

The examples in this section demonstrate various ways to count the occurrences of a character

or text string in a range of cells. Figure 7-4 shows a worksheet that demonstrates these

examples. Various text appears in the range A1:A10 (named
Data
); cell B1 is named
Text.

Figure 7-4:
This worksheet demonstrates various ways to count characters in a range.

The companion CD-ROM contains a workbook named
counting text in a range.

xlsx
that demonstrates the formulas in this section.

Entire cell contents

To count the number of cells containing the contents of the
Text
cell (and nothing else), you can

use the COUNTIF function. The following formula demonstrates:

=COUNTIF(Data,Text)

For example, if the
Text
cell contains the string
Alpha
, the formula returns 2 because two cells in the

Data range contain this text. This formula is not case sensitive, so it counts both
Alpha
(cell A2) and

alpha
(cell A10). Note, however, that it does not count the cell that contains
Alpha Beta
(cell A8).

The following array formula is similar to the preceding formula, but this one is case sensitive:

{=SUM(IF(EXACT(Data,Text),1))}