Search form

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:

=COUNTIF(range,criteria)

  • 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".

COUNTIF example, counting numerical values

  • 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.

Excel COUNTIF example using text criteria

  • You can do a date comparison, but be warned that date comparisons in Excel can be tricky.

 

Want to learn more? Try these lessons:

Similar lessons

Our Comment Policy.

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.

Add a comment to this lesson

Comments on this lesson

Using a CounIF and And Statement

Submitted by on

I am trying to get a total count for a column only if that column AND another column meet specific criteria.

For example:
Column A Column B Column C Column D
Install Description Date Issued Date Completed
Maintain Description Date Issued Date Completed

In this case, I want to know how many lines say "install" only if the "date completed" column is blank. What formula would I use?

Easy to follow

Submitted by on

I just used your post to help me with a problem at work. Your example involving counting the letter 'J' was straight forward and able to rectify a business problem that I had. Thank you!

Add comment