Microsoft Office Tutorials and References
In Depth Information
Advanced Counting Formulas
This next array formula operates like the MODE function except that it works with both text and
values:
{=INDEX(Data,MATCH(MAX(COUNTIF(Data,Data)),COUNTIF(Data,Data),0))}
If there is more than one most frequent value, the preceding formula returns only the
first in the list.
Counting the occurrences of specific text
The examples in this section demonstrate various ways to count the occurrences of a character
or text string in a range of cells. Figure 7-4 shows a worksheet that demonstrates these
examples. Various text appears in the range A1:A10 (named Data ); cell B1 is named Text.
Figure 7-4: This worksheet demonstrates various ways to count characters in a range.
The companion CD-ROM contains a workbook named counting text in a range.
xlsx that demonstrates the formulas in this section.
Entire cell contents
To count the number of cells containing the contents of the Text cell (and nothing else), you can
use the COUNTIF function. The following formula demonstrates:
=COUNTIF(Data,Text)
For example, if the Text cell contains the string Alpha , the formula returns 2 because two cells in the
Data range contain this text. This formula is not case sensitive, so it counts both Alpha (cell A2) and
alpha (cell A10). Note, however, that it does not count the cell that contains Alpha Beta (cell A8).
The following array formula is similar to the preceding formula, but this one is case sensitive:
{=SUM(IF(EXACT(Data,Text),1))}
 
Search JabSto ::




Custom Search