Countif function with comparison operators in Excel

The Countif function

In Excel, the Countif function is used to count how many cells in a range are an exact match to a specific value. Consider the example below where the number of employees who have taken exactly 14 days holiday are counted.

14 is simply entered as the criteria in the Countif function.

But what if you don’t want to know how many employees have taken exactly 14 holiday days, and instead are more interested in the number of employees who have taken 14 or more holiday days?

Let’s take a closer look at the ingredients you’ll need to find that answer: The syntax of the Countif function is as follows:

=Countif(range, criteria)

• Range, specifies the cells to be included in the analysis and criteria determines which cells will be counted.
• Criteria, determines which cells will be counted

Comparison operators other than ‘=’

A comparison operator, as the name suggests, carries out a comparison between two values. The default comparison operator used by Excel to assess the criteria in a Countif function is the equals sign (=). So, if you don’t instruct Excel otherwise, it will look for an exact match.

However, you can use other comparison operators in your Countif criteria. You just need to add them yourself. In the table below are comparison operators you might want to use with Countif.

Since the default operator for the Countif function is equals, its very important that the comparison operators other than the equal to be enclosed in double quotes.

For simplicity purposes, we will focus only on the Countif function in combination with the greater than operator (>) to explain the concept of using comparison operators. However, keep in mind that that the other comparison operators can also be used.

Comparison operator in the criteria of Countif functions

Back to our example. If you want to know how many employees have taken more than 14 holiday days based on the information above, you might think you could simply enter the condition >14 (greater than 14) as your criteria in the formula. Unfortunately, it’s not that easy. If you do so, Excel will return the following error:

Excel returns an error because it does not recognize >14 as a number. Fear not, this doesn’t mean Excel is not capable of performing this calculation; the key is to make sure that Excel interprets your criteria as a text entry rather than a number. In fact, there are three solutions to this problem. In the following sections, we walk you through each solution and then tell you our personal favourite at the end.

Approach 1: Enter hard-coded criteria with double quotation marks in the formula

Approach 2: Enter the criteria in a separate cell and refer to that cell in the formula

Other functions and comparison operators

Similarly to how you can combine the Countif function with a comparison operator other than equals to (=) such as greater than (>), for comparison operators you can apply the same principle to the following functions that are related to Countif:

• Countifs function
• Sumif(s) function
• Averageif(s) function
• Maxif(s) function
• Minif(s) function

Count on another lesson for more practice

We believe that the best practice for using the Countif function for a comparison operator is to enter the entire criteria (including the comparison operator) in a separate cell and then refer to that cell in the formula. But, this may take some getting used to, so give the Countif function a try the next time you are working with a comparison operator in Excel.

To learn different approaches to using Countif such as how to define ranks, use Countif plus Countif to rank on more than one criterion, to separate equal ranks, or count the frequency of duplicates, sign-up at 5miles for a free two-week trial, and go through the 15 different Countif focused Excel Challenges.