Being able to count cells between two values in Excel is a valuable skill. For example, you may need to count how often values fall within a given range of values. Similarly, you may want to count how many students fall between two sets of test scores. In this tutorial, you’ll learn how to use Excel to count cells that are larger than one value and smaller than another.
By the end of this tutorial, you’ll have learned the following:
- How to use the Excel COUNTIFS() function to count cells between two values
- How to count cells inclusively that are larger than a value and smaller than another value
Count Cells Between Two Values in Excel with COUNTIFS()
In this section, you’ll learn how to count cells between two values in Excel. In order to do this, we’ll use the Excel COUNTIFS() function, which lets you count the number of cells that meet one or more conditions.
How can you count cells between two values in Excel?
In order to count cells between two values in Excel, you can use the COUNTIFS() function. You can write =COUNTIFS(range, “>”&lower_limit, range, “<“&upper_limit). This allows you to easily count the number of cells between two values.
Let’s take a look at the Excel COUNTIFS() function and its arguments to better understand how to use it:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)We can see that the function has two required arguments and up to 254 optional arguments. Let’s take a look at these arguments in more detail:
| Argument | Description | Required? |
|---|---|---|
criteria_range1= | The range of cells to apply the condition to | Yes |
criteria1= | The condition to apply, including any of the logical operators that may be required | Yes |
[criteria_range2, criteria2] | The additional range of cells to look apply the conditions to and the conditions to apply | No |
The function has two required parameters. However, if you only use the first two parameters, the function behaves in the exact same way as the Excel COUNTIF() function.
In order to add more conditions, you need to add pairs of criteria and ranges. This means that even if you’re applying multiple conditions to the same range, you need to add the range multiple times.
Let’s take a look at an example of how we can count cells that are between two values – a lower limit and an upper limit:
In the example above, we loaded a dataset that contains regional sales data. We want to count cells where the sales value falls between 2200 and 3000. In cell F4, we entered the following formula:
=COUNTIFS(C3:C13, ">"&F2, C3:C13, "<"&F3)Let’s break down what we’re doing in the formula above:
- We first insert the range of C3:C13, covering our sales data
- We include the condition “>”&F2. Note that we’re wrapping the logical operator,
>, in double quotes and then concatenating it with the cell reference. Without using double quotes, the formula would fail. - We then include our second set of range and criteria. We use the same range but check for cells lower than the value in F3.
We can see how easy it is to count cells that are between two values. In the following section, you’ll learn how to count cells that are greater or equal to a value and less than or equal to another value in Excel.
Count Cells Greater or Equal to a Value and Less than or Equal to a Value in Excel
In the previous section, you learned how to count cells between two values exclusively – meaning that we only counted values that were between two values but didn’t include them. In this section, we’ll learn how to count cells that are between two values inclusively – meaning that we’ll include values that are equal to or larger than one value and less than or equal to another value.
In order to do this, we can re-use the same formula as we did above, but use the >= and <= operators, respectively. Let’s see what this new formula looks like:
=COUNTIFS(C3:C13, ">="&F2, C3:C13, "<="&F3)Now, let’s take a look at the same example we used above, but count the values inclusively.
In the example above, we used the greater than or equal to operator and the less than or equal to operators, respectively. We used them to include the lower and upper bounds of our ranges. By doing this, we counted 2 more cells than we did before.
Conclusion
In this tutorial, you learned how to count cells between two numbers in Excel. This can be a tremendously useful skill to learn. For example, it allows you to count how many students fall between a range of test scores. Similarly, it allows you to count how many events, such as log-in attempts, happened between a lower and upper bound. You first learned how to use the COUNTIFS() function to count cells between two values. Then, you learned how to count cells that included the upper and lower limits in their counts.
Additional Resources
To learn more about related topics, check out the resources below: