HOW TO COUNT THE OCCURRENCES OF A TEXT STRING
In the cell that you
want the result to appear in, enter the appropriate formula from the following
examples.
How to Count the
Occurrences of a Number
Use this formula
=SUM(IF(range=number,1,0))
where range is the
range that you want to search, and number is the number that you want to count.
NOTE: This formula
must be entered as an array formula. To enter an array formula, press
CTRL+SHIFT+ENTER.
How to Count the
Occurrences of a Text String
Method 1
Use this formula
=SUM(IF(range="text",1,0))
where range is the
range that you want to search, and text is the text that you want to find (the
text must be enclosed in quotation marks).
NOTE: The above
formula must be entered as an array formula. To enter an array formula, press
CTRL+SHIFT+ENTER.
Method 2
Use the COUNTIF()
function to count the occurrences of a text string. For example, use the
formula
=COUNTIF(range,"text")
where range is the
range of cells that you are evaluating, and text is the text string that you
want to count instances of (note that text must be enclosed in quotation
marks).
NOTE: This formula
must be entered as an array formula. To enter an array formula, press
CTRL+SHIFT+ENTER.
Wildcard characters can be used within the
COUNTIF function.
The asterisk character (*) represents more
than one character. For example, to count all the cells in the range a1:a10
that contain an "x," you can use the following formula:
=COUNTIF(a1:a10,"*x*")
The question mark
character (?) can also be used to represent one wildcard character -- for
example, to count all cells in the range whose second character is the letter,
such as "ax" or "bx."
=COUNTIF(a1:a10,"?x*")
×