How to Use the COUNTIF Function in Excel

Download 10 Excel Templates for Marketers [Free Kit]

Excel can do more than just simple math. That’s thanks to its bevy of built-in functions and min-formulas that simplify the creation of more complex formulas.

In my decade-long experience with Excel, I’ve found that one of the more useful functions 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 to manually count yourself.

The COUNTIF function in Excel counts the number of cells in a range that meet the given criteria. It doesn’t total the cells; it simply counts them. I’ve found it 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 have to sort the addresses by ZIP code to do it.

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

1. =COUNTIF()

Begin by entering the following into the cell where you want to place the answer:

=COUNTIF()

For this example, we’ll use a grocery list that I’ve written. The different items I want to buy are sorted by type, like vegetables and fruit.

2. Define a range of cells.

For the COUNTIF function to work, you have to enter two arguments between the parentheses — the range of cells you’re looking at and the criteria you want to match.

Place your cursor within the parentheses and either manually enter the range of cells (e.g., D1:D20) or use your mouse to highlight the 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 a comma.

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

=COUNTIF(A2:A35,)

4. Define your search criteria.

You now need to enter the criteria or value that you want to count after the comma, surrounded by quotation marks.

In our example, let’s say you’re looking to see how many vegetables are on your list. In this instance, the criteria you’re counting is Vegetable, and your function should now look like this:

=COUNTIF(A2:A35, “Vegetable“)

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