Count Cells Between Two Numbers
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This calculator counts how many cells in a given range fall between two numbers you specify, including or excluding the boundaries as you choose. You enter the cell range and the lower and upper limits, and you get back the count of matching cells, plus the total number of cells in the range. It is a direct implementation of the COUNTIFS formula used in spreadsheet software.
Formula
The calculation uses the standard COUNTIFS function, which counts cells that meet multiple criteria. For a range R, lower bound L, and upper bound U, the formula is:
Count = COUNTIFS(R, “>&=”&L, R, “<="&U) (when boundaries are included)
If boundaries are excluded, the formula becomes COUNTIFS(R, “>”&L, R, “<"&U).
Variables:
- R – the cell range (e.g., A1:A10).
- L – the lower numeric bound.
- U – the upper numeric bound.
The result is an integer count of cells whose values satisfy both conditions simultaneously. The total cells in the range is simply the number of cells in the selected area, which you can compute with ROWS(R)*COLUMNS(R).
Worked Example
Using the default values: range A1:A10, lower bound 5, upper bound 15, include boundaries = Yes.
- List the values in A1:A10: suppose they are 3, 7, 12, 15, 4, 9, 20, 5, 11, 6.
- Apply the condition: value >= 5 AND value <= 15.
- Check each cell: 3 (no), 7 (yes), 12 (yes), 15 (yes), 4 (no), 9 (yes), 20 (no), 5 (yes), 11 (yes), 6 (yes).
- Count the “yes” cells: 7.
So the calculator returns 7 for the count, and 10 for the total cells in the range.
How to Use This Calculator
Enter the range in standard spreadsheet notation (e.g., B2:B100). The calculator does not validate the range against an actual sheet; it simply assumes the range is correct and counts based on the numbers you provide. You must also enter the lower and upper bounds. Use the “Include boundaries” dropdown to decide whether values exactly equal to the bounds should be counted.
For example, if you want to count exam scores between 70 and 79 inclusive, set lower=70, upper=79, include=Yes. If you want scores strictly above 70 and strictly below 80, set include=No.
When to Use This Count
This kind of count is common in data analysis, quality control, and academic grading. You might use it to:
- Count how many products fall within a target weight range.
- Count how many students scored in a particular grade band.
- Count how many days had temperatures between two values.
- Count how many transactions are between $100 and $500.
The result is always a whole number, so it is easy to interpret. The total cells in the range helps you see what fraction of the data falls within the bounds.
Common Mistakes and Misconceptions
A frequent mistake is forgetting that the range must be a single contiguous block. If you need to count across multiple separate ranges, you must use multiple COUNTIFS and add them together. Another mistake is mixing up inclusive and exclusive bounds: if you want values between 5 and 10 but not including 5 or 10, you must choose “No” for include boundaries.
Also, this calculator treats all cells as numbers. If your range contains text or empty cells, they are ignored in the count, which is the same behavior as COUNTIFS in spreadsheet software. The total cells in range counts all cells, including empty ones, so the count may be less than the total.
One misconception is that COUNTIFS can handle multiple ranges of different sizes. In standard spreadsheet software, all ranges in COUNTIFS must have the same dimensions; this calculator assumes a single range, so that issue does not arise.
Alternatives and Related Functions
In spreadsheet software, you might also use:
- COUNTIF – for a single condition.
- COUNT – counts numeric cells only.
- COUNTA – counts non-empty cells.
- SUMIFS – sums values that meet multiple criteria.
If you need to count values that are less than a lower bound or greater than an upper bound, you can subtract the between-count from the total. For example, to count values outside the range, use total – count_between.
For a quick manual count, you could sort the data and visually inspect, but that is error-prone for large ranges. This calculator automates the process and gives you the exact number instantly.
Limitations of the Formula
The COUNTIFS formula is designed for numeric comparisons. It does not work directly with dates unless the dates are stored as numeric values (which they are in most spreadsheet software). It also does not handle wildcards or text patterns; for text matching you would need COUNTIF with wildcards, which is not covered here.
The calculator assumes that your range contains only numbers or blanks. If there are errors (like #DIV/0!), they are ignored in the count, but they are still counted in the total cells, which might be confusing. In practice, you should clean your data before using this count.
Frequently Asked Questions
How do I count cells between two numbers in Excel?
Use the COUNTIFS function: =COUNTIFS(A1:A10,">=5",A1:A10,"<=15") for inclusive bounds. For exclusive, use ">5" and "<15". This calculator replicates that formula.
Does the count include the boundary numbers?
That depends on your choice. If you select “Include boundaries = Yes”, the count includes values equal to the lower and upper bounds. If you select “No”, those exact values are excluded.
What if my range has empty cells or text?
Empty cells and text cells are ignored in the count, just like in COUNTIFS. The total cells in range includes them, so your count may be lower than the total.
Can I count between two numbers across multiple columns?
Yes, as long as the range is a single contiguous block, like A1:C20. The calculator will count all cells in that block that meet the criteria.
Why is my count different from what I expected?
Check that your bounds are correct and that you have the right inclusive/exclusive setting. Also verify that the range is correctly typed and that there are no hidden rows or filtered data that might affect the actual cell values.
Is there a way to count values outside the range?
Yes, subtract the between-count from the total number of numeric cells. For example, if total is 10 and count between is 7, then 3 are outside. You can also use COUNTIFS with "<"&L and ">"&U.
Related
Formula used
in rng | Cell range | - | A1:A10 | 0 in low | Lower bound | - | 5 | 0 in high | Upper bound | - | 15 | 0 in incl | Include boundaries | 1=Yes, 0=No | 1 | 0 out cnt | Count between | cells | COUNTIFS(rng,">="&low,rng,"
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.