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

Search JabSto ::

Custom Search