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:
Search JabSto ::




Custom Search