Cart 0

How to use the COUNTIF Function in Excel

Posted by Roger Hyttinen on

Screenshot of movie listing spreadsheet

The COUNTIF function in Excel comes in handy if you need to know how many occurrences of something appears in a range of data. For instance, if you have a list of movies, you could view a count of the number of movies before 2014.

The COUNTIF function consists of 2 arguments: The cell range and the criterion. Using the example above, let’s say that the Year cell range for our movies is B2:B400. If we wanted to find out how many movies in our list were made prior to 2014, our formula would read:

=COUNTIF(B2:B400, “<2014”)

As we can see, the arguments are separated by a comma and the last argument (the criteron) is surrounded by quotes.

Screenshot of CountIF Function

An another example, if we wanted to find out how many of our customers are from Illinois, the formula would read (assuming the state field range is B1:B800):

=COUNTIF(B1:B800, “IL”)

You can also use wildcards in the formula, in which the asterisk symbol (*) can replace any number of characters. Using our movies list as an example, if we used the asterisk after the word “Superman”, we would get a count of all of our movies whose title begins with Superman (Superman 1, Superman 2, Superman Returns). To do this, our formula would read:

=COUNTIF(B1:B400, “Superman*”)

So keep the COUNTIF function in mind should you ever need to find the number of occurrences of specific data in your dataset.  It can be a real time-saver.

Share this post

← Older Post Newer Post →

Leave a comment

Please note, comments must be approved before they are published.