How to Use The COUNTIF Function

How to Use the COUNTIF Function Feature Image

In this blog post, we’re going to look at how to use the COUNTIF function. The COUNTIF function counts the number of cells in a range that match a certain criterion. For example, imagine you have a range of cells that contains sales data, and you want to know how many times you have achieved a sales target. This is easy with the COUNTIF function; you simply count the number of cells equal to, or above, the sales target.

In this post you will learn how to use the COUNTIF function to count the number of cells that contain specific numbers, text strings, and dates. We will also look at how to use the COUNTIF function with different comparison operators and wildcard characters.

Quick Example

Using the example mentioned in the introduction, imagine that we have a list of sales data that lists the values of sales for each month of a year, as shown in the image below.

Example Sales Data

We want to search this list to find out how many months we achieved or exceeded the sales target of $14,000. We can achieve this by using the following formula.

=COUNTIF($C$5:$C$16,”>=14000″)

We simply insert the formula into cell B2.

 COUNTIF Sales Figures Example

The formula returns the value 9, which means the sales target was met or exceeded 9 times.

Syntax

The COUNTIF function is used to count the number of cells, in a range of cells, that meet a specific criterion.

The syntax of the COUNTIF function is COUNTIF(range, criteria).

ParametersDescription
rangeThe range parameter is the range of cells against which you want to test the criteria parameter. The range parameter can contain either numbers, a range of cells, or a named range.
CriteriaThe criteria parameter can be either a number, an expression, a cell reference, or a text string, against which the range of cells in the range parameter is evaluated. With the exception of a cell reference, the criteria parameter value must be enclosed in quotation marks ().
The criteria parameter is not case-sensitive. Therefore, the COUNTIF function will match "DOG," "Dog," and "dog" exactly the same way.

Counting Values

You can use the COUNTIF function to count the number of cells, in a range, whose contents are greater than, less than, or equal to a certain value. In order to do this, we use one of the comparison operators in the table below.

OperatorExampleDescription
>>10greater than a specified value, in this example greater than 10
<<10less than a specified value , in this example less than 10
=10equal to a specified value , in this example equal to 10
<><>10not equal to a specified value, in this example not equal to 10
>=>=10greater than or equal to a specified value, in this example greater than or equal to 10
<=<=10less than or equal to a specified value, in this example less than or equal 10

We are going to use comparison operators with the COUNTIF function to count and determine various pieces of information in the following table, containing people’s names and ages.

Sample Age Data

People Who Are Over 50

If we want to work out how many people in a list are over the age of 50, we need to use the greater than operator (>) as shown in the following formula.

=COUNTIF($C$3:$C$16,”>50″)
  • range – the range parameter is a reference to the range of cells C3:C16. The cells in this range of cells will be checked against the criteria parameter.
  • criteria – notice that we place the greater than operator (>) and the value we checking against (50) inside quotation marks “>50” to form the criteria.

We simply insert the formula into cell F2 as shown in the following image.

COUNTIF Age Data

As can be seen from the image above, we have five people over the age of 50.

People Who Are Under 30

If we want to work out how many people in our list are under the age of 30 (lucky people), we need to use the less than operator (<) as shown in the following formula.

=COUNTIF($C$3:$C$16,”<30″)
  • range – the range parameter is unchanged from the previous example, the range of cells C3:C16.
  • criteria – like before, we place the less than operator (<) and the value we are checking against (30) inside quotation marks “<30” to form the criteria

Again, simply insert the formula into cell F2 as shown in the following image.

COUNTIF Age Data People Under 30

As can be seen from the image above, we have two people under the age of 30.

People Who Are Aged 40

Finally, if we want to work out how many people in our list are 40 years of age, we need to use the equals operator (=) as shown in the following formula.

=COUNTIF($C$3:$C$16,”=40″)
  • range – the range parameter is unchanged from the previous example, the range of cells C3:C16.
  • criteria – like before, we place the equals operator (=) and the value we checking against (40) inside quotation marks “=40” to form the criteria.

Again, simply insert the formula into cell F2 as shown in the following image.

COUNTIF age data people aged 40

As can be seen from the image above, we have one person who is 40 years of age.

Using a Named Range for the Range Parameter

You can use named ranges for the range parameter of the COUNTIF function. If you are not familiar with named ranges then you can find out more by clicking the link to my blog post here.

To do this, we need to create named ranges for the list of names. To do this, select cells B3:B10 and type “Names” in the name box and press enter as shown in the image below.

COUNTIF Create Named Range

Once this is done, press enter. Now, if we want to count the number of cells that contain the text string Lee Baker in the named range Names we use the following formula.

=COUNTIF(Names,”Lee Baker”)
  • range – this time, the range parameter is the named range
  • criteria – the criteria this time is simply the text string “Lee Baker”. With no comparison operators, the COUNTIF with count the number of cells with values that match the text string irrespective of the case of the text string or cell value.

To use this formula, we insert into cell B2 as shown in the image below.

COUNTIF Named Range Formula

The formula returns the value 3, which is correct. We have three cells that contain the name Lee Baker.

Wildcard Characters

You can use wildcard characters with the COUNTIF function. For example, you may want to search a list of names all names with the surname Smith. To do this, we would use the following formula.

=COUNTIF(B3:B9,”*Smith”)
  • range – the range parameter is simply a reference to the range of cells B3:B9
  • criteria – the criteria parameter uses the asterisk (*) wildcard character at the front of the text Smith to form the criteria parameter “*Smith”. This criterion means that the COUNTIF function will count all cells that finish with the text “Smith,” irrespective of the text before the name Smith.

We simply insert the formula into a worksheet, as shown in the image below.

COUNTIF Wildcard Characters Formula

The formula returns the value 3, which is correct; there are 3 people with the surname Smith.

For more information on wildcard characters, check out my post by clicking here.

Counting Blank Cells

You can use the COUNTIF function to count blank cells. For example, we might have a list of cells containing names and we want to know how many blank cells there are in the list.

To do this, we could use the following formula:

=COUNTIF(B3:B10,”<>”&”*”)
  • The range parameter is simply a reference to the range of cells, B3:B10.
  • The criteria parameter uses a combination of the “not equal to” operator (<>) and the asterisk (*) wildcard character, to form the criteria parameter “<>”&”*”. Because the asterisk (*) wildcard character can represent any character, the criteria checks to see if a cell value is not equal to any character (i.e. blank).

We insert the formula into cell B2, as shown in the image below.

COUNTIF Blank Cells Formula

The formula returns the value 2, which is correct. There are two blank cells in our list.

Please note that while you can use the COUNTIF function to count non-blank cells, Excel does have a function specially for this: the COUNTBLANK function.

COUNTIF and Dates

You can use the COUNTIF function to count the number of cells that are less than, equal to, or greater than a specific date. You can also use the COUNTIF function with the other date functions to achieve very useful outcomes.

For example, imagine you have a range of cells containing dates when invoice payments are due as shown in the image below.

Example Invoice Date Data

You can use the COUNTIF function in conjunction with the TODAY function to count the number of invoices who’s payment date has passed.

To do this, we use the following formula:

=COUNTIF(C5:C27,”<“&TODAY())

Breaking this formula down:

  • The TODAY function takes no parameters and returns a date serial number. If you are not familiar with the TODAY function click here to find out more.
  • Using the TODAY function in combination with the less than (<) operator forms the criteria of the COUNTIF This criterion tests to see if a cell value has a date earlier than today’s date.
  • range – the range parameter is the range of cells, C5:C27, that contain date values that will be tested to see if they are earlier than today’s date.

To use this formula, we insert it into cell C2.

COUNTIF and TODAY Functions

On the day the blog post was written, 1 October 2016, 13 invoices had payment dates that had passed.

Common Errors

The COUNTIF function will return the #REF! error if you delete the range of cells that the range parameter is referencing. For instance, if the formula references a range of cells on a separate worksheet that is deleted after the formula is created.

Criteria Parameter String Length

The criteria parameter will return an error if a text string length is longer than 255 characters. However, to get around this problem, you can use the CONCATENATE function or the, concatenate (&) operator to connect multiple text strings together. For example, the formula below uses the concatenate (&) operator to concatenate to strings “Lee” and “ Baker” together.

=COUNTIF($B$3:$B$10,”Lee”&” Baker”)

Note that in the second text string it starts with a space then the text Baker. This is because the final text string has a space between the words Lee and Baker. We can use this formula to count the number of cells that contain the text string “Lee Baker” as shown in the image below.

COUNTIF and Concatenated Text Strings

The formula returns the value 3, which is correct.

Wrap-up

That’s it for this blog post. Hopefully, you found it useful. The COUNTIF function can be used with text, wildcards characters, numbers, and dates. You have a large degree of control over the criterion you use to determine which cells you count using the various comparison operators. It can be used in combination with other functions such as the TODAY function. Hopefully you have seen how the COUNTIF function can be used in several different circumstances, there are certainly many more than have been presented in this blog post.

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my resources page.

To get a copy of the example spreadsheet used in this blog post and receive a copy of our newsletter, sign up to our email list below.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit