Use COUNTIF to count the cells in a range that match certain values
Sometimes you need to count the number of cells in a spreadsheet that contain a value or set of values. The COUNTIF function allows you to do this by counting only those cells in the range that meet the criteria you set. This lesson explains how to use COUNTIF, and provides an example of how you can use it.
COUNTIF function syntax
The COUNTIF function has the following syntax:
- range is the set of cells which you want to count.
- criteria is what you use to set which cells will be counted.
Note that you need to provide both range and criteria for this function to work - you'll get an error if you try to leave either of them out.
The key to mastering the COUNTIF function lies in learning how to define your criteria correctly.
- You can use a numerical comparison, such as the following examples:
- To count "all cells which contain 500", you would enter 500 as your criteria value.
- To count "all cells which are less than 500", you would enter "<500" as your criteria. Note that the quotation marks are required for any criteria that are not a number.
- To count "all cells which are greather than or equal to 500", you would enter ">=500".
- You can also do a text comparison, as in these examples:
- To count "all cells that contain the word July", your criteria would be "July". Once again, note the use of quotations. If you don't enter quotation marks, or you use apostrophes rather than quotation marks, then your COUNTIF formula will not work as you intended.
- To count "all cells that start with the letter S or higher", your criteria would be ">=S".
- To count "all cells that start with the letter J", your criteria would be "J*". In this example, the '*' is what's known as a wildcard, which tells Excel to count any cell that starts with the letter "C" regardless of what letters might follow.
- You can do a date comparison, but be warned that date comparisons in Excel can be tricky.