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.

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

** **

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**).

Parameters | Description |
---|---|

range | The 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. |

Criteria | The 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.

Operator | Example | Description |
---|---|---|

> | >10 | greater than a specified value, in this example greater than 10 |

< | <10 | less than a specified value , in this example less than 10 |

= | 10 | equal to a specified value , in this example equal to 10 |

<> | <>10 | not equal to a specified value, in this example not equal to 10 |

>= | >=10 | greater than or equal to a specified value, in this example greater than or equal to 10 |

<= | <=10 | less 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.

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

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.

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.

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.

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.

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.

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.

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.

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

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.

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.