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:

COUNTIF(range, criterion)

syntax-of-google-sheets-countif-greater-than-and-less-than

Arguments

ARGUMENT REQUIREMENT Function
range Required The range that is tested against the ‘criterion’.
criterion Required The pattern or test to apply to ‘range’.

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.

CRITERIA FORMULA EXAMPLE Function
The number is greater than =COUNTIF(B5:B9, “>1400”) Count cells where values are greater than 1400.
The number is less than =COUNTIF(B5:B9,”<1400″) Count cells where values are less than 1400.

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.

google-sheets-countif-greater-than-and-less-than


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.
=COUNTIF(C5:C9,">1200")

google-sheets-countif-greater-than-value-criteria-value-criteria

  • 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.
=COUNTIF(C5:C9,">"&C8)

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.
=COUNTIF(D5:D9,"<2000")

google-sheets-countif-less-than-numerical-values

  • 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.
=COUNTIF(D5:D9,"<"&D5)

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


Similar Readings


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.
=COUNTIF(E5:E9,">1/05/2022")

google-sheets-countif-greater-than-date-criteria

  • 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.
=COUNTIF(E5:E9,">"&E6)

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-
=COUNTIF(E5:E9,"<1/05/2022")

google-sheets-countif-greater-Less-than-date-criteria

  • 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.
=COUNTIF(E5:E9,"<"&E5)

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-
=COUNTIF(C5:C9,">"&1200)-COUNTIF(C5:C9,"<"&1400)

πŸ”Ž Formula Breakdown:

  • COUNTIF(C5:C9,”>”&1200)

Firstly, this function will count data in cell range C5:C9 that are greater than 1200 and will return 4.

  • COUNTIF(C5:C9,”<“&1400)

Secondly, the function will count data in cell range C5:C9 that are less than and equal to 1400 and will return 3.

  • =COUNTIF(C5:C9,”>”&1200)-COUNTIF(C5:C9,”<“&1400)

Lastly, this function will subtract the result of the second formula, 3, from the result of the first formula, 4, and return the value 1.

google-sheets-multiple-countif-greater-than-less-than-functions

  • 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.
=COUNTIF(C5:C9,">"&C8)-COUNTIF(C5:C9,"<="&C7)

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

Jawadul Islam Chowdhury

Jawadul Islam Chowdhury

Hello! This is Jawad. I create Google Spreadsheets-related content for OfficeWheel. I enjoy doing research and solving Google Spreadsheet-related problems. I love to learn new things and teach them to others.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo