COUNTIF with Greater than and Less than Criteria in Google Sheets

In our day-to-day use of Google Sheets, we sometimes need to count values that are greater or less than a given value. The COUNTIF function is a handy function that can be used to count values that are greater than or less than a given value. In this article, you will learn how you can count values using Google Sheets COUNTIF greater than and less than functions.

Introduction of COUNTIF Function in Google Sheets

The COUNTIF function is a combination of the COUNT and IF functions. It is a statistical function that is used to count cells in a range while the given criteria are satisfied.

Syntax

The syntax of the COUNTIF function is as follows:

Arguments

Output

The formula COUNTIF(B5:B9, “>1400”) will look for values greater than 1400 across the range B5:B9 and will return 2.

Logical Expression Criteria of COUNTIF Function in Google Sheets

To create logical expressions within criteria, we can use the mathematical operators ‘<’, ‘>‘, and ‘=‘. In this article, we will learn to count values using COUNTIF and greater than (<) or less than (>) operators. To understand how the function works with logical criteria, look at the table below.

3 Suitable Examples of COUNTIF with Greater than and Less than Criteria in Google Sheets

The COUNTIF function allows us to count cells based on numerical values as well as the date by using logical expressions greater than and less than. Both the numerical value and the cell reference can be used in the criteria.

Let’s say you have a dataset of sales of products throughout the year. You now want to count values that are greater, less, or lie between reference values.

1. Adding up Cells Based on Numerical Values Criteria

You can count the numerical values using the Google Sheets COUNTIF function for greater than and less than criteria. Both the numerical value and the cell reference are valid in the criteria.

1.1: COUNTIF Greater than Value Criteria

To count the Unit sales that are greater than 1200 units follow the below steps-

📌 Steps:

• First of all, select cell H4 and type the following formula.

• Then, press Enter and the result of the COUNTIF function will be shown in cell H4. Here, a numerical value of 1200  is used in logical criteria.

• Similarly, you can use a cell reference in the logical criteria.

Read More: How to Use COUNTIF Function with OR Logic in Google Sheets

1.2: COUNTIF Less than Value Criteria

To count the Sales prices that are less than $2000 units follow the below steps.

📌 Steps:

• First of all, select cell H7 and type the following formula.

• Afterward, press Enter and the result of the COUNTIF function will be shown in cell H7. Here, a numerical value of 2000 is used in logical criteria.

• A cell reference can also be used in the logical criteria.

Read More: Google Sheets Count Cells from Another Workbook with COUNTIF Function

Similar Readings

• Use COUNTIF If Cell Contains Specific Text in Google Sheets
• How to Execute Case Sensitive COUNTIF in Google Sheets
• Use COUNTIF Function to Count Checkbox in Google Sheets

2. Counting Cells Based on the Date Criteria

You can use the COUNTIF function to count the cells based on the date criteria. In this case, you can both the numerical value and the cell reference.

2.1: COUNTIF Greater than Date Criteria

To count the Date After May 2022 follow the below steps-

📌 Steps:

• In the beginning, select cell H4 and type the following formula.

• Now, press Enter. Cell H4 will display the outcome of the COUNTIF function. Here, a numerical date value of 1/05/2022  is used in logical criteria.

• Using cell reference in place of the numerical date value produces a similar result.

Read More: COUNTIF Function with “Not Equal to” Criterion in Google Sheets

2.2: COUNTIF Less than Date Criteria

To count the Date Before May 1, 2022, follow the below criteria-

📌 Steps:

• First, cell H7 and enter the following formula-

• Next hit Enter and cell H7 will show the result of the COUNTIF function.

• You can use cell reference as well which will provide a similar result.

Read More: [Fixed!] COUNTIF Function Is Not Working in Google Sheets

3. Using Multiple COUNTIF Functions

You can also use multiple COUNTIF functions to find greater than and less than criteria in Google Sheets. Suppose you need to count Unit Sales Between 1200 and 1400. In this case, you need to use multiple COUNTIF greater or less than functions, Follow the below steps.

📌 Steps:

• Initially, select cell C4 and type the following formula-

• After that, click Enter. The number of Unit values that are in-between 1200 and 1400 will be shown.

• In the same way, you can use cell references to get the same result.

Read More: How to Use VLOOKUP with COUNTIF Function in Google Sheets

Things to Remember

• Make sure to enclose the criteria argument in quotation marks.

Conclusion

Hopefully, now you can count values using Google Sheets COUNTIF function for greater than and less than criteria. Please comment below with your doubts or suggestions regarding this article. Visit our website, OfficeWheel, for more articles on using functions.

Related Articles

• How to Use COUNTIF for True Condition in Google Sheets
• Google Sheets Add Calculated Field for Pivot Table with COUNTIF
• How to Use COUNTIF for Cells Not Equal to Text in Google Sheets