To count cells that contain values less than a given number in a range, use the generic formula based on the COUNTIF function.
Generic Excel Formula to count the number of cells with values less than a given number:
=COUNTIF(range, "<number")In the picture above, “range” is the cell range containing numbers, and “number” is the threshold value. We want to count cells if they contain less than the given number.
The result is 2, and we will explain why.
How to count cells less than a given number
The COUNTIF function uses two required arguments: range and criteria.
=COUNTIF(range, criteria)Set up the arguments for the COUNTIF formula:
- range: C3:C8
- criteria: “<120”
The numbers we want to test using the criteria using range C3:C8. So to count cells less than 120, the COUNTIF formula looks like this:
=COUNTIF(C3:C8,"<120")Note: Take a look at cell C4, which contains 120. The logical test, in this case, is FALSE. In the example, we use the “less than” (<) operator instead of the “less than equal to” (=<) operator. Small but important difference!
The proper syntax is critical in Excel (not just in Excel).
Don’t forget to insert a quote before and after the criteria. The logical operator (<) is joined with a numeric value (120) and supplied as text.
The formula returns 2, so we have two cells that are less than 120.
Greater than or equal to – COUNTIF
We use the list mentioned above in the first example. Now, use the “greater than or equal to” operator (>=) to count cells in the range.
Formula to count cells greater than or equal to 120:
=COUNTIF(C3:C8,">=120")As you see, 4 cells met the criteria.
Related Formulas: