Microsoft Office Tutorials and References

In Depth Information

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

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 ori-

ginal 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. See Chapter 7 for additional examples.

Figure 14-16 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-16:
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: