Microsoft Office Tutorials and References
In Depth Information
Using Single-Cell Array Formulas
To add the three smallest values, you could use a formula like this:
=SUM(SMALL(Data,1), SMALL(Data,2), SMALL(Data,3)
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 :
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 14-14 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
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 returns the third-smallest value of 2.
Figure 14-14: 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
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
values in a range that meet some criterion (for example, values greater than 12).