The COUNTIF function in Excel is used to count cells in a given range that meet a specified criteria. It’s similar to the COUNT function but not exactly the same. Imagine someone asks you to count the number of people in a room. That’s the COUNT function. Now imagine that same person asks you to count only the people in the room who are wearing a blue shirt. That’s the COUNTIF function.
Take a look at the spreadsheet sample below. In column A we have a list of employee names. In column B, we have the number of items each of them have sold. In cell B8, we list the minimum number of sales required for an employee to earn a bonus.
Let’s say for budgeting purposes, you want to know how many employees have earned the bonus so far. In other words, how many employees have sold 25 or more items to date. We can do this with the COUNTIF function in Excel.
The COUNTIF function takes two paremeters: range and criteria. The range in our example is the items sold which is represented by the cells B2 through B6. To format this properly for Excel, we express it as B2:B6.
The criteria parameter is the test each cell in the range has to pass in order to be counted. In our example, this means the items sold needs to be greater than or equal to 25. One way we could do this is to use >=25 as our criteria. This works but is somewhat limiting.
A more flexible way is to put the value 25 in a cell (cell B8 in our sample) and then reference that cell in our criteria. In this case, we would use “>=”&B8 as our criteria.
That last part is a little tricky. The >= has to be in quotation marks because it doesn’t reference a cell. Then we need to use the ampersand (&) symbol to concatenate the cell reference. Don’t worry, as complicated as “>=”&B8 looks, Excel just sees it as >=B8.
Now, it’s time to use the COUNTIF function in Excel to put this all together. Here’s what you would put in cell B9:
You can see the result in the updated spreadsheet sample below.
Check out our Excel Lesson Videos for more examples on how to use the COUNTIF function.
Partial matches (wildcards)
If you only require a partial match in your COUNTIF function criteria, you can use Excel’s wildcard characters.
Using an asterisk in your criteria tells Excel that any number of consecutive characters can be found in place of the asterisk. Using a question mark in your criteria tells Excel that one and only one character can be found in place of the question mark.
Let’s look at the sample spreadsheet below to run a few examples of how the asterisk and question mark wildcards work.
If we want to count all of the students registered for a Chemistry class, regardless of whether it is Chemistry 101 or Chemistry 103, we can use the following COUNTIF function in any blank cell:
If we want to count all of the students registered for only a 100-level Chemistry class (thus excluding Chemistry 201 in our list), we can use the following COUNTIF function in any blank cell:
To count all of the students registered for 200-level courses, no matter the subject, we can use the following COUNTIF function which uses both the asterisk and question mark wildcards in any blank cell:
Now you know how to use the COUNTIF function in Excel to give you the flexibility to count only cells in a range that meet a specified criteria.