Excel 2010 Bible

Chapter 16: Introducing Array Formulas

373

This formula works fine, but using an array formula is more efficient. The following array formula

returns the sum of the three smallest values in a range named Data:

{=SUM(SMALL(Data,{1,2,3}))}

The formula uses an array constant as the second argument for the SMALL function. This generates

a new array, which consists of the three smallest values in the range. This array is then passed to

the SUM function, which returns the sum of the values in the new array.

Figure 16.15 shows an example in which the range A1:A10 is named Data. The SMALL function is

evaluated three times, each time with a different second argument. The first time, the SMALL func-

tion 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 returns the third smallest value of 2.

FIGURE 16.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

{-5,0,2)

The formula returns the sum of the array (–3).

Counting text cells in a range ..................................................................................

Suppose that 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 val-

ues in a range that meet 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 for-

mula 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