In this article, I’ll discuss how you’ll count cells greater than and less than using the function with 6 practical examples. First, I’ll focus on the basics of the function, and then I’ll show the use of the COUNTIF function for greater than, and 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
You can download our practice workbook from here for free!
Introduction to COUNTIF Function in Excel
Using the COUNTIF function, we can easily count the number of cells based on a given condition. 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-
Here,
- range – The range of cells to count.
- criteria – The criteria that control which cells should be counted.
6 Examples of Using COUNTIF to Count Cells in Excel for Greater Than and Less Than Conditions
Say, we have a sample dataset of an employee salary sheet in the following figure. Now, we have to count cells that fulfill our criteria using the COUNTIF function.
Let’s get started.
1. COUNTIF for Counting Cells Greater Than a 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 steps.
📌 Steps:
- First and foremost, select a blank cell e.g. I5, and insert the following formula.
=COUNTIF(F5:F15,">4500")- Subsequently, press the Enter key.
Thus, you’ll get the gross salary count for greater than $4500 as 8.
Note:
“>” means greater than, “<“ means less than, and “>=” means greater than or equal.
Read More: Excel COUNTIF Function to Count Cells Greater Than 0
2. COUNTIF for Counting Cells Less Than a Value
Again, you can count the number of employees having a gross salary of less than $4500. Go through the steps below to do this.
📌 Steps:
- First, click on cell I5.
- Following, insert the formula below.
=COUNTIF(F5:F15,"<4500")- Subsequently, hit the Enter key.
As a result, you will calculate the gross salary count for less than 4500 and the result is 2.
Read More: How to Use COUNTIF Between Two Numbers (4 Methods)
3. COUNTIF for Counting Cells of Greater Than Value in a Particular Cell Reference
Now, say, you want to use the COUNTIF function for greater than a cell value. Say, you want to count the Basic Salary count as being greater than 4500. You can accomplish this by using the steps below.
📌 Steps:
- At the very beginning, click on cell H8 and put the value that you want to set as criteria.
- Afterward, click on cell I5 and insert the following formula.
=COUNTIF(D5:D15,">"&H8)- Subsequently, hit the Enter key.
Consequently, you will find the basic salary count for specific criteria using a cell value.
Read More: How to Apply COUNTIF Between Two Cell Values in Excel
4. COUNTIF Function for Counting Cells of Less Than a Value in 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. Follow the steps below to achieve this target.
📌 Steps:
- First, click on cell H8 and put your criteria value.
- Next, click on cell I5 and insert the following formula.
=COUNTIF(D5:D15,"<"&H8)- Subsequently, hit the Enter key.
As a result, you will find your desired result for the count of employees who have a basic salary of less than $4500.
Read More: How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)
Similar Readings
- How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)
- COUNTIF Excel Example (22 Examples)
- How to Use COUNTIF with WEEKDAY in Excel (3 Easy Ways)
- COUNTIF Between Two Dates in Excel (4 Suitable Examples)
- Apply COUNTIF Function in Multiple Ranges for Same Criteria
5. COUNTIF Function for Counting Dates Succeeded by Another Date
Imagine, you have to find the number of employees who joined the office after 1 Jul 2020. In that case, you have to use the COUNTIF function in the following steps below.
📌 Steps:
- First and foremost, click on cell H8 and put your criteria date (7/1/2020 here).
- Afterward, click on cell I5 and insert the following formula.
=COUNTIF(C5:C15,">"&H8)- Subsequently, hit the Enter key.
Thus, you will get the count of employees who has joined after 1 July 2020.
Read More: COUNTIF Date Is within 7 Days
6. COUNTIF Function for Counting Dates Preceded by Other Dates
Furthermore, if you want to count the number of employees who joined before 1 Jul 20, you have to use the following steps below.
📌 Steps:
- Initially, click on cell H8 and insert the criteria date.
- At this time, click on cell I5 and insert the following formula.
=COUNTIF(C5:C15,"<"&H8)- Following, press the Enter key.
Thus, you will find the number of employees that joined before 1 July 2020.
Read More: Count Blank Cells with Excel COUNTIF Function: 2 Examples
How to Use COUNTIF Function to Apply Multiple Greater or Less Than Criteria in Excel
You can also use the COUNTIF function to apply multiple criteria in the same or different ranges.
Within Same Range:
Imagine, you want to find the number of employees having a gross salary of greater than $4000 and less than $5000. You have to involve multiple criteria within the same range here. Follow the steps below to do this.
📌 Steps:
- First, click on cell I5 and insert the following formula.
=COUNTIF(F5:F15,">4000")-COUNTIF(F5:F15,">=5000")- Subsequently, hit the Enter key.
Thus, You will find the count for employees with a gross salary greater than 4000 and less than 5000.
Within Different Ranges:
Now, imagine that you want to count the number of employees that fulfill two criteria from two different ranges. 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 can accomplish this by following the steps below.
📌 Steps:
- At the very beginning, click on cell I5.
- Following, insert the formula below.
=COUNTIF(F5:F15,">4500")-COUNTIF(E5:E15,">=1000")- Subsequently, hit the Enter key.
Consequently, you will find the number of employees who have a gross salary greater than 4500 and other allowances less than 1000.
Read More: COUNTIF That Does Not Contain Multiple Criteria in Excel
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
So, in this article, I have shown you 6 practical examples to use the COUNTIF function to find greater than and less than specific values. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.
And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!
Related Articles
- Excel COUNTIFS Not Working (7 Causes with Solutions)
- COUNTIF vs COUNTIFS in Excel (4 Examples)
- How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)
- VBA COUNTIF Function in Excel (6 Examples)
- Compare Two Tables and Highlight Differences in Excel (4 Methods)
- How to Use COUNTIF Function with Array Criteria in Excel
