Count Cells Less than a Particular Value [COUNTIF] in Excel (Formula)

In Excel, if you want to count cells that have a value less than or lower than a particular value then you need to use the COUNTIF function. With this function, you can specify a range of values to count and then a criteria number to count the cells less than that.

In this formula tutorial, we will look at some examples to understand it.

COUNTIF to Count Less Than Cells

In the following example, we have a list of values in the column (a list of 1000 numbers). Now, you need to count the cells which are less than 45. As I said, you need to use COUNTIF, we will write this formula in cell B1.

You can use the following steps:

1. First, start typing the COUNTIF function in cell B1 (Enter =COUNTIF).
2. After that, in the first argument of the function refer to column A, you can also refer to the A1:A101 only.
3. Now, type a comma and get in the second argument.
4. From here, you need to specify the argument for the criteria. So, type <=45 with double quotation marks.
5. In the end, type the closing parentheses and hit enter.

And once you hit enter it returns the count of cells with values less than or equal to 46.

You can also specify a cell that contains the criteria value. In the following example, you have a value in cell B1 that you want to use as criteria and then refer to the cell in the function.

Now if you see, we have the formula in cell B4. Now in this formula, we have referred to the range A1:A101.

And in the second argument, where we need to specify the criteria, we have entered the greater than and equal to the operator using double quotation marks.

After that, we used an ampersand and referred to cell B1.

This way, if you want to change the criteria you don’t need to change the value from the formula itself, you can edit it from cell B1.

Points to Remember

1. When you use <=, excel takes it as lower than and equal to. so, if you use <=45 this means all values which are to 45. but only that referring lower-than only, for example, <45 less 45.
2. You can also create multiple criteria by using a COUNTIFS function and apart from this you can use COUNTIF OR in Excel to create an or logic while counting the less than values.