Download the Practice Workbook
6 Examples of Using COUNTIF to Count Cells in Excel for Greater Than and Less Than Conditions
We have a sample dataset of an employee salary sheet. We’ll count cells that fulfill our criteria using the COUNTIF function.
Example 1 – COUNTIF for Counting Cells Greater Than a Value
Let’s count the number of employees who get a gross salary greater than $4,500.
Steps:
- Select a blank cell for the result (such as I5) and insert the following formula.
=COUNTIF(F5:F15,">4500")- Press the Enter key.
Note:
“>” means greater than, “<“ means less than, and “>=” means greater than or equal.
Read More: Excel COUNTIF Function to Count Cells Greater Than 0
Example 2 – COUNTIF for Counting Cells Less Than a Value
Let’s count the number of employees who have a gross salary of less than $4,500.
Steps:
- Enter the following formula in the result cell and press Enter.
=COUNTIF(F5:F15,"<4500")
Read More: How to Use COUNTIF Between Two Numbers (4 Methods)
Example 3 – COUNTIF for Counting Cells That Have Greater Value Than a Particular Cell Reference
Steps:
- We put a separate cell, H8, for the criterion and listed a salary value.
- Enter the following formula in the result cell I5 and press Enter.
=COUNTIF(D5:D15,">"&H8)
Read More: How to Apply COUNTIF Between Two Cell Values in Excel
Example 4 – COUNTIF Function for Counting Cells With Lower Values Than a Particular Cell
Steps:
- Click on cell H8 and put your criteria value.
- Enter the following formula in the result cell I5 and press Enter.
=COUNTIF(D5:D15,"<"&H8)
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
Example 5 – COUNTIF Function for Counting Dates Succeeded by Another Date
Let’s find how many employees joined after 1 Jul 2020.
Steps:
- Click on cell H8 and put your criteria date (7/1/2020).
- Insert the following formula in the result cell I5 and press Enter.
=COUNTIF(C5:C15,">"&H8)
Read More: COUNTIF Date Is within 7 Days
Example 6 – COUNTIF Function for Counting Dates Preceded by Other Dates
Let’s count the number of employees who joined before 1 Jul 20.
Steps:
- Click on cell H8 and insert the criteria date.
- Insert the following formula in the result cell I5 and press Enter.
=COUNTIF(C5:C15,"<"&H8)
Read More: Count Blank Cells with Excel COUNTIF Function: 2 Examples
How to Use the COUNTIF Function to Apply Multiple Greater or Less Than Criteria in Excel
Case 1 – Within Same Range:
Let’s find the number of employees with a gross salary greater than $4,000 but less than $5,000.
Steps:
- Insert the following formula in the result cell I5 and press Enter.
=COUNTIF(F5:F15,">4000")-COUNTIF(F5:F15,">=5000")
Case 2 – Within Different Ranges:
Let’s find the number of employees with a gross salary greater than $4,500 and other allowances lower than $1,000.
Steps:
- Insert the following formula in the result cell I5 and press Enter.
=COUNTIF(F5:F15,">4500")-COUNTIF(E5:E15,">=1000")
Read More: COUNTIF That Does Not Contain Multiple Criteria in Excel
Things to Keep in Mind
- You need to use the double quotes (“ ”) in the COUNTIF function when creating conditional checks.
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
