Excel 2010 Bible

Part II: Working with Formulas and Functions

374

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))}

Cross-Reference
This general array formula type (that is, an IF function nested in a SUM function) is very useful for counting.
See Chapter 13 for additional examples of IF and SUM functions. n

Figure 16.16 shows an example of the preceding formula in cell C7. The array created by the IF

function is

{0,1,1,1;1,0,0,0;1,0,0,0;1,0,0,0;1,0,0,0}

Notice that this array contains four rows of three elements (the same dimensions as the range).

Here is a slightly more efficient variation on this formula:

{=SUM(ISTEXT(A1:D5)*1)}

This formula eliminates the need for the IF function and takes advantage of the fact that

TRUE * 1 = 1

and

FALSE * 1 = 0

FIGURE 16.16

An array formula returns the number of text cells in the range.

Eliminating intermediate formulas ..........................................................................

One key benefit of using an array formula is that you can often eliminate intermediate formulas in

your worksheet, which makes your worksheet more compact and eliminates the need to display

irrelevant calculations. Figure 16.17 shows a worksheet that contains pre-test and post-test scores

for students. Column D contains formulas that calculate the changes between the pre-test and the