Excel 2019 Bible

Chapter 18: Understanding and Using Array Formulas

18

This general array formula type (that is, an IF function nested in a SUM function) is useful for count-

ing. See Chapter 13, “Using Formulas for Conditional Analysis,” for additional examples of IF and SUM

functions.

Figure 18.16 shows an example of the preceding formula in cell C7. The array created by this
formula is as follows:

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

FIGURE 18.16

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

Notice that this array contains five rows of four 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

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 18.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 post-test scores.