Counting Cells Below a Value in Excel using COUNTIF

Using COUNTIF to Count Cells with Values Less Than a Specified Number

Let’s say you have a list of 1000 numbers in column A, and you want to count the cells that are less than 45. To do this, you can use the and follow these steps:

1. Type =COUNTIF in cell B1 to start the formula.
2. In the first argument, refer to column A by typing A1:A1000.
3. Type a comma to move to the second argument.
4. In the criteria argument, enter “<=45” (without quotes).
5. Close the parentheses and hit enter.

This formula will count the cells that have values less than or equal to 45.

Using a Cell as Criteria in COUNTIF

Alternatively, you can use a cell to specify the criteria value. In this example, we have a value in cell B1 that we want to use as the criteria:

1. Type =COUNTIF(A1:A1000,”<=”&B1) in cell B4.
2. The range A1:A1000 refers to the cells you want to count.
3. The second argument specifies the criteria – “<=” means less than or equal to, and the ampersand (&) combines it with the value in cell B1.
4. This way, you can change the criteria by editing the value in cell B1, instead of changing the formula itself.

Points to Keep in Mind

Remember that when you use the “<=” operator, Excel counts cells that are less than or equal to the specified number. If you only want to count cells that are less than the specified number, use the “<” operator instead.

You can also use function to create multiple criteria, or use OR in Excel to create an OR logic when counting cells with values less than a specified number.