Being able to count cells that are less than a particular value in Excel is an important skill. For example, you may need to count how many sales happened that didn’t meet a particular target. Or perhaps, you need to know how many users haven’t logged on a certain number of times. Thankfully, Excel provides many different ways in which to count cells that are less than or equal to a particular value. In this tutorial, you’ll learn how to count cells that are less than or equal to a value in Excel, using the COUNTIF() function and the COUNTIFS() function.
By the end of this tutorial, you’ll have learned the following:
- How to use the COUNTIF() function to count cells that are less than a value in Excel
- How to use the COUNTIF() function to count cells that are less than or equal to a value in Excel
- How to use the COUNTIFS() function to count cells that are less than a value and meet another condition
How to Count Cells Less Than a Particular Value in Excel
How can you count cells that are less than a value in Excel?
To count how many cells are less than a value in Excel, you can use the COUNTIF() function. Simply use the function as: =COUNTIF(range, “<“&value). The COUNTIF() function accepts both the range of values and the criteria to count.
Let’s dive into this a little bit deeper. The Excel COUNTIF() function is a function that allows you to count values that meet a particular condition – such as whether a value is less than given value.
Let’s take a look at the COUNTIF() function and how it’s written in Excel:
=COUNTIF(range, criteria)As you can see from the code block above, the function takes two arguments. Let’s take a closer look at them in the table below:
| Argument | Description | Required? |
|---|---|---|
range= | The range of cells to apply the condition to | Yes |
criteria= | The condition to apply, including any of the logical operators that may be required | Yes |
Because of this, we can simply pass in the range we want to count and the criteria using the less than operator, <. Let’s take a look at an example of what this looks like in Excel:
In the example above, we used the Excel COUNTIF() function to count cells that are less than a value. In this case, we checked whether or not the values were less than 2,200, which we stored in cell F2. We used the following formula to count the number of cells that are less than a value:
=COUNTIF(C3:C13, "<"&F2)Note that we’re wrapping the less than operator in double quotes – without this, the function will raise an error. We then concatenate the operator with the cell that holds our value.
In the following section, you’ll learn how to count cells that are less than or equal to a particular value.
How to Count Cells Less Than or Equal to a Particular Value in Excel
In order to count cells that are less than or equal to a particular value in Excel, we can use a similar method to the example above. In fact, all we need to do is change the logical operator to the less than or equal to operator, <=.
Let’s take a look at an example, where we want to count the number of cells that are less than or equal to 2,200:
In the example above, we used the Excel COUNTIF() function to count cells that are less or equal to a value. In this case, we checked whether or not the values were less than or equal to 2,200, which we stored in cell F2. We used the following formula to count the number of cells that are less than a value:
=COUNTIF(C3:C13, "<="&F2)Note that we’re wrapping the less than or equal to operator in double quotes – without this, the function will raise an error. In this case, the function returned 6.
In the final section, you’ll learn how to use the Excel COUNTIFS() function to count cells that are less than a valueto and meet another condition.
How to Count Cells Less Than a Value and Meet Another Condition in Excel
In this section, you’ll learn how to use Excel to count cells that are less than a value and also meet another condition. This can be done using the Excel COUNTIFS() function, which allows you to count cells that meet multiple conditions. This can be done by passing in pairs of ranges and criteria. Because of this, we can count the number of cells that are less than or equal to a value and where the region is equal to North.
In the example above, we used the following formula:
=COUNTIFS(C3:C13, "<="&F2, B3:B13, "North")We passed in two ranges and criteria in the formula above. The first checks whether the cells in the range C3:C13 are less than or equal to 2200. The second set checks whether the Region is equal to “North”. It’s important to note that the COUNTIFS() function uses an AND operator under the hood, meaning that both conditions must be met in order for the cells to be counted.
Conclusion
In this tutorial, you learned how to count cells that are less than a value in Excel. Being able to do this an a valuable skill to be able to count specific amounts of data. For example, you might want to count how often a sale was made of a certain value or how mangy users logged in a certain number of times. You first learned how to use the Excel COUNTIF() function to count cells that were either less than or less than or equal to a certain value. Then, you learned how to use the Excel COUNTIFS() functionto count cells that are less than a value and meet another condition.
Additional Resources
To learn more about related topics, check out the resources below: