Count if cells less than a given number

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”)

How to count cells less than a given number

Here are the steps to count cells with values less than a given number in Excel:

  1. Open Excel.
  2. Type the following formula into a cell:
  3. =COUNTIF(range, “<number”)
  4. Replace range with the range of cells to evaluate (e.g., A1:A10), and replace number with the value you want to use as the threshold.
  5. Press Enter.
  6. The formula will return the number of cells in the specified range with values less than the given number.

Example

In this example, we have a dataset in range C3:C8 that contains order quantities for various locations. The goal is to count how many orders are below 120. The formula in cell E3 uses the COUNTIF function with the criteria “<120” enclosed in double quotes to identify and count all values less than 120.

use-double-quotes

The result is displayed in cell E3, representing the total count of orders that meet the condition.

Explanation

Here’s a detailed explanation of how the formula =COUNTIF(C3:C8,”<120″) works:

The COUNTIF function counts the number of cells in a specified range that meet a given condition. This is the set of cells the function evaluates, specifically the range from cell C3 to C8. The function examines each cell in this range to determine if it satisfies the specified condition.

The criteria argument (“<120”) define the condition to be evaluated. In this instance, “<120” indicates that the function checks whether the value of each cell in the range is less than 120.

During the evaluation phase, the function processes each cell in the range C3:C8 individually. If a cell’s value is less than 120, it is included in the count. If not, the cell is excluded.

Finally, the formula calculates and returns the total count of cells in the range C3:C8 that meet the condition of having a value less than 120.

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.

count-cells-greater-than-or-equal-to-a-given-number

Formula to count cells greater than or equal to 120:

=COUNTIF(C3:C8,”>=120″)

As you see, 4 cells met the criteria.

Related Formulas: