Microsoft KB Archive/28262

{| = XL: Counting Cells in a Range that Contain Text String =
 * width="100%"|

Last reviewed: August 20, 1997

Article ID: Q28262

SUMMARY
To count the number of cells in a range that contain a particular character or characters, use the following array formula in a single cell (array formulas must be entered by pressing COMMAND+ENTER):

=SUM(IF(NOT(ISERROR(SEARCH(&quot;search text&quot;,range))),1)) This formula searches for the occurrence of the search text in a cell, returns #VALUE! if an occurrence is not found, and then counts the number of nonerror values. For example, suppose you want to search for the text &quot;my&quot; in the following range of cells:

+---+-+  |   |    A    | +---+-+  | 1 | mytext  | +---+-+  | 2 | text    | +---+-+  | 3 | textmy  | +---+-+  | 4 | temyxt  | +---+-+ The formula =SUM(IF(NOT(ISERROR(SEARCH(&quot;my&quot;,$A$1:$A$4))),1)) will return a value of 3 because &quot;my&quot; occurs three times in the range A1:A4.
 * }