How to Count the Occurrences of a Number or Text in a Range in Excel
Whether you use Excel to store numeric or textual information, the program provides a host of functions that you can use to manipulate and count that information. If you have a long range of numbers or text and you need to know how many times a particular number or piece of text appears in a cell, use can use the COUNTIF function. COUNTIF allows you to specify a number or text string to count, and will return a number, which will correspond with the number of times that the searched-for item appears in the range, that you can then use in other calculations.
Other People Are Reading
Instructions
-
-
1
Open the Microsoft Excel 2010 file that holds the data you want to look through.
-
2
Click on an empty cell where you want the result of your formula to appear.
-
-
3
Type “COUNTIF(“ into the cell. Do not press "Enter," as you will still need to add the arguments to the function.
-
4
Click on the top-left cell in the range of cells that you want to search for your text or number. Hold the mouse button down and drag the mouse to the bottom-right cell of that range. Release the mouse button and you will see the reference for that range of cells appear in your formula. If you want to search through an entire column or range of columns, you can just type “A:C” into the formula instead of clicking on cells, where “A” is the first columns in the range and “C” is the last column.
-
5
Type a comma into your formula, then enter in the text or number that you want to search for. If you are searching for a piece of text, you will need to enclose it in double quotation marks. Then type a close parenthesis and press “Enter” to complete the formula. The number of occurrences of your searched-for number or text will appear in the cell.
-
1
Tips & Warnings
As a completed formula example, if you wanted to search the first ten cells in the first three columns for the number five, you would use a formula similar to: =COUNTIF(A1:C10,5).
When choosing a range for the COUNTIF function, the range must be continuous on the spreadsheet. Additionally, the range cannot include the cell where the formula is located, as this will cause a circular reference.
