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.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.