How to Use the COUNTIF Function in Excel

Excel can do much more than just simple math. This is thanks to its built-in functions and mini-formulas that simplify the creation of more complex formulas.

In my decade-long experience with Excel, one of the more useful functions I have found is the COUNTIF function.

You can use COUNTIF to count the number of cells that contain a specific value or range of values. It's easier to use COUNTIF than counting manually.

How to Use the COUNTIF Function in Excel

The COUNTIF function in Excel counts the number of cells in a range that meet given criteria. It does not form an aggregate of cells; It simply counts them. I have found this useful for counting cells that contain a specific value or range of values.

For example, let's say you have a spreadsheet that contains customer contact information, including street addresses and zip codes. You can easily use the COUNTIF function to count how many customers live in a given zip code – and you don't even need to sort addresses by zip code to do this.

Let's work through the step-by-step process.

1.=COUNTIF()

Start by entering the following in the cell where you want to answer:

=COUNTIF()

For this example, we'll use the grocery list I wrote. The different items I want to buy are sorted by type, such as vegetables and fruits.

2. Define a range of cells.

To make the COUNTIF function work, you need to enter two arguments between the parentheses – the Category The cells you are looking at and criteria You want to match.

Place your cursor within the brackets and either manually enter the range of cells (for example, D1:D20) or use your mouse to highlight a range of cells in your spreadsheet.

Assuming your zip code values ​​are in column D from row 1 to row 20, the function should now look like this:

=COUNTIF(A2:A35)

3. Add comma.

Next, type a comma after the range, like this:

=COUNTIF(A2:A35,)

4. Define your search criteria.

Now you need to enter the criteria or values ​​that you want to count followed by commas surrounded by quotation marks.

In our example, let's say you're trying to see how many vegetables you have on your list. In this example, the parameter you are counting is vegetableAnd your function should now look like this:

=COUNTIF(A2:A35, ,vegetable,,

Note that your criteria can be a number (“10”), text (“Los Angeles”), or any other cell (C3). However, if you reference another cell, you do not surround it with quotation marks. Criteria are not case-sensitive, so you can enter “red,” “red,” or “red” and get the same results.

5. Activate the function.

Press Enter, and the function is activated, returning the number of cells matching your argument.

Tips for using the COUNTIF function

Many users, including myself, have found that you can use the COUNTIF function in many different ways besides counting specific values. Here are three tips I recommend for maximizing your use of the COUNTIF function.

Use wildcard characters for partial matches.

You do not need to reference any specific values ​​or criteria. If you only know part of the value you want to count, you can use the * wildcard character to match any value in that part of the value.

For example, let's say you have a list of addresses. If you want to match all ZIP codes starting with the number 46 (such as 46032, 46033, and 46450), you would enter 46 after this , Wildcard, like this:

=COUNTIF(D1:D20,,46*,,

You can use wildcard characters at the beginning or end of the value string. For example, enter the following to count all cells ending with the letter “polis”:

=COUNTIF(D1:D20,,*police,,

This will count chambers that include the cities of Indianapolis and Minneapolis.

Count values ​​that are greater than or less than a number.

If you're working with numbers, you may want to count cells with values ​​greater or less than a given value. You do this by using the mathematical signs greater than (>) and less than (<).

To count all cells whose value is greater than a given number, such as 10, enter this:

=COUNTIF(D1:D20,,>10,,

To count cells greater than or equal to a number, enter this:

=COUNTIF(D!:D20,>=10,,

To count all cells whose value is less than a given number, enter this:

=COUNTIF(D1:D20,<10,,

To count cells whose value is less than or equal to a given number, enter this:

=COUNTIF(D1:D20,<=10,,

You can also count cells with a value that is not equal to a specific number. For example, to count cells whose number is not equal to 10, enter this:

=COUNTIF(D1:D20,<10,,

In all of these examples, remember that the criteria, including the less than, greater than, and equal signs, must be enclosed within quotation marks.

Count one or the other value.

The COUNTIF function can also be used to count multiple criteria – that is, cells that contain one or another value.

For example, you may want to count customers who live in Los Angeles or San Diego. You do this by using Two COUNTIF functions with + between them, like this:

=COUNTIF(D1:D20,,angel,)+COUNTIF(D1:D20,,San Diego,,

To add even more values, enter another + and COUNTIF functions.

If you want to get even more out of Excel, check out our article on how to use Excel like a pro. You'll find 29 powerful tips, tricks, and shortcuts that will make using Excel even easier.

launch

If you want to count the number of items matching specific criteria, the COUNTIF function is the way to go. You could just sort that column and count the entries manually, but it's much easier to use COUNTIF.

Now, try it and save yourself some time.