Microsoft Office Tutorials and References

In Depth Information

**Using Single-Cell Array Formulas**

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

This general array formula type (that is, an IF function nested in a SUM function) is very

useful for counting. Refer to Chapter 7 for additional examples.

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

function is as follows:

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

Figure 14-15:
An array formula returns the number of text cells in the range.

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

A variation on this formula follows:

{=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