Part II: Working with Formulas and Functions
Figure 18.15 shows an example in which the range A1:A10 is named Data. The SMALL func-
tion is evaluated three times, each time with a different second argument. The first time,
the SMALL function has a second argument of 1 , and it returns –5. The second time, the
second argument for the SMALL function is 2 , and it returns 0 (the second smallest value
in the range). The third time, the SMALL function has a second argument of 3 , and it
returns the third smallest value of 2.
FIGURE 18.15
An array formula returns the sum of the three smallest values in A1:A10.
Therefore, the array that’s passed to the SUM function is as follows:
{-5,0,2)
The formula returns the sum of the array (–3).
Counting text cells in a range
Suppose you need to count the number of text cells in a range. The COUNTIF function
seems like it might be useful for this task—but it’s not. COUNTIF is useful only if you need
to count values in a range that meets some criterion (for example, values greater than 12).
To count the number of text cells in a range, you need an array formula. The following array
formula uses the IF function to examine each cell in a range. It then creates a new array (of
the same size and dimensions as the original range) that consists of 1s and 0s, depending
on whether the cell contains text. This new array is then passed to the SUM function, which
returns the sum of the items in the array. The result is a count of the number of text cells in
the range:
{=SUM(IF(ISTEXT(A1:D5),1,0))}
