Page 303 - Excel 2007 Bible

19_044039 ch14.qxp 11/21/06 11:06 AM Page 260
Part II
Working with Formulas and Functions
Total occurrences in a range
To count the total number of occurrences of a string within a range of cells, use the following array formula:
{=(SUM(LEN(Data))-SUM(LEN(SUBSTITUTE(Data,Text,””))))/
LEN(Text)}
If the Text cell contains the character “B”, the formula returns 7 because the range contains seven instances
of the string. This formula is case sensitive.
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))}
The preceding formula is one of those “classic” Excel formulas that gets passed around the
NOTE
NOTE
Internet. I don’t think anyone knows who originated it.
Useful as it is, this formula does have a serious limitation: If the range contains any blank cells, it returns an
error. The following array formula solves this problem:
{=SUM(IF(COUNTIF(Data,Data)=0,””,1/COUNTIF(Data,Data)))}
CROSS-REF To find out how to create an array formula that returns a list of unique items in a range, refer
CROSS-REF
to Chapter 18.
The companion CD-ROM contains a workbook that demonstrates this technique. The file is
ON the CD-ROM named count unique.xlsx.
ON the CD-ROM
Creating a frequency distribution
A frequency distribution basically comprises a summary table that shows the frequency of each value in a range.
For example, an instructor may create a frequency distribution of test scores. The table would show the count
of A’s, B’s, C’s, and so on. Excel provides a number of ways to create frequency distributions. You can
n Use the FREQUENCY function
n Use the Analysis ToolPak add-in
n Create your own formulas
n Use a pivot table
A workbook that demonstrates these four techniques appears on the companion CD-ROM. The
ON the CD-ROM file is named frequency distribution.xlsx.
ON the CD-ROM

260