COUNTIF Greater Than and Less Than [with Free Template]

Using the COUNTIF function, we can easily count the number of cells based on a given condition.

In this article, I’ll discuss how you’ll count cells greater than and less than using the function. First, I’ll focus on the basics of the function, and then I’ll show the use of the COUNTIF function for greater than, less than values for multiple different situations.

Finally, we’ll see the greater than and less than combinedly with two diverse conditions.

Download Practice Workbook

COUNTIF Function Basics

COUNTIF is an Excel function for counting cells within a range that fulfills a specific condition. This function can count cells containing dates, numbers, and text. The syntax of the function is

=COUNTIF (range, criteria)

range – The range of cells to count.

criteria – The criteria that control which cells should be counted.

COUNTIF for Counting Cells for Greater Than and Less Than Conditions

We see an employee salary sheet in the following figure. Now we have to count cells that fulfill our criteria using the COUNTIF function.

Employee Salary Sheet

Let’s get started.

1. COUNTIF for Counting Cells of Greater Than Value

If you want to count the number of employees who get a gross salary greater than $4500, you can use the COUNTIF function in the following way.

For this, select a blank cell e.g. I5, and insert the following formula

=COUNTIF(F5:F15,">4500")

Here, F5:F15 is the range of Gross Salary and >4500 is the criteria.

Now, press Enter and you’ll the gross salary of 8 employees is greater than $4500.

COUNTIF for Counting Cells of Greater Than Value

Note.>” means greater than, “<“ means and “>=” means greater than or equal

2. COUNTIF for Counting Cells of Less Than Value

Again, you can count the number of employees having a gross salary of less than $4500.

Select a blank cell for finding those numbers and insert the following formula.

=COUNTIF(F5:F15,"<4500")

Here, F5:F15 is the range of Gross Salary and <4500 is the criteria.

After pressing Enter, you’ll get that only two employees have a gross salary of less than $4500.

COUNTIF for Counting Cells of Less Than Value

3. COUNTIF for Counting Cells of Greater Than Value for A Particular Cell

When the condition is given in a particular cell in the dataset, you have to use the following COUNTIF formula. This way, you’ll count the employees having a basic salary of greater than $4500. Before inputting the formula, select an output cell and press Enter when inputting is finished.

=COUNTIF(D5:D15,">"&H8)

Here, D5:D15 is the range basic salary and H8 is the cell reference.

COUNTIF for Counting Cells of Greater Than Value for A Particular Cell

4. COUNTIF Function for Counting Cells of Less Than Value for A Particular Cell

Similarly, you can utilize the function for counting the employees who have a basic salary of less than $4500 using cell reference. Before inputting the formula, select an output cell and press Enter when inputting is finished.

=COUNTIF(D5:D15,"<"&H8)

Here, D5:D15 is the range basic salary and H8 is the cell reference where the condition is inserted.

COUNTIF Function for Counting Cells of Less Than Value for A Particular Cell

5. Counting Dates Greater Than Another Date

Imagine you have to find the number of employees who joined the office after 1-Jul-2020. In that case, just select a blank cell, input the formula, and press Enter.

=COUNTIF(C5:C15,">1-Jul-20") 

Here, C5:C15 is the joining date of the employees and >1-Jul-20 is our condition.

Counting Dates Greater Than Another Date

6. Counting Dates Less Than Other Dates

Furthermore, if you want to count the number of employees who joined before 1-Jul-20, you have to use the following formula after selecting a blank cell.

=COUNTIF(C5:C15,"<1-Jul-20")

Here, C5:C15 is the joining date of the employees and >1-Jul-20 is our condition.

In the following screenshot, you see 9 employees joined before the date.

Counting Dates Less Than Other Dates

7. COUNTIF Function for Counting Greater than and Less Than

Let’s imagine, you want to find the number of employees having a gross salary of greater than $4000 and less than $5000.

Just choose the output cell and input the formula

=COUNTIF(F5:F15,">4000")-COUNTIF(F5:F15,">=5000")

Here, F5:F15 is the range of gross salary, >4000 and >=5000 are the criteria

While pressing Enter, you’ll get that 5 employees have the gross salary that meets the criteria

COUNTIF Function for Counting Greater than and Less Than

Assuming that you want to count the number of employees that fulfill two criteria from two different fields.

Like, you wish to find the number of employees having a gross salary of greater than $4500 and other allowances less than $1000.

In such circumstances, you might use the formula after choosing a blank cell.

=COUNTIF(F5:F15,">4500")-COUNTIF(E5:E15,">=1000")

Here, F5:F15 is the range of gross salary, E5:E15 is the range of other allowances, >4500  and >=1000 are the conditions.

COUNTIF Function for Counting Greater than and Less Tha

Things to Keep in Mind

  • Be attentive while using the double quotes (“ ”) in the COUNTIF function.
  • Also, be careful about the file name, file location, and Excel extension name.

Conclusion

This is how you can count greater than and less than using the COUNTIF function that meets different criteria. If you’ve any suggestions, please share them in the comments section.

Thanks for being with me.


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo