This article explains how you can use worksheet functions in Microsoft Excel for Mac to count the number of occurrences of a specific number or text string in a range of cells on a worksheet. The "More Information" section of this article contains sample formulas that you can use to do this.
In the cell that you want the result to appear in, enter the appropriateformula from the following examples.
How to Count the Occurrences of a Number
Use this formula
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
Use this formula
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.
Use the COUNTIF() function to count the occurrences of a text string. Forexample, use the formula
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).
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:
The question mark character (?) can also be used to represent one wildcardcharacter -- for example, to count all cells in the range whose secondcharacter is the letter, such as "ax" or "bx."
XL2002 XL2000 MacXLX Mac XLX XL2001 XL98 XL97 XL7 XL2003 XL2007 XL2010 kbhowto answer text string