Count Cells Between Two Numbers in Excel and Google Sheets
Purpose
Counting values that fall within a range — a score between 70 and 89, an amount between 500 and 1000,…
Counting values that fall within a range — a score between 70 and 89, an amount between 500 and 1000, a date in the current quarter — is one of the most frequent analytical questions I answer. It is how grade distributions are built, how income brackets get sized, how stock inventory in a healthy range is reported. The trick is that you cannot do it with a single COUNTIF because COUNTIF only accepts one criteria at a time. You need the COUNTIFS function, which accepts multiple range-criteria pairs and applies AND logic across them. You pass the same range twice — once with the lower bound using >=, once with the upper bound using <= — and COUNTIFS returns the count of cells that satisfy both conditions simultaneously. This formula is the natural combination of the count cells greater than a value and count cells less than a value patterns, and it should be in every analyst's regular toolkit.
Syntax
✓ All versions
✓ Google Sheets
= Same syntax
=COUNTIFS(range, ">=low", range, "<=high")
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The cells to examine. Specified twice — once for the lower-bound condition and once for the upper-bound condition. Both references must point to the exact same cells for a single-column range check. |
| criteria1 (lower) | Required | The lower boundary as a quoted string such as ">=70" for at-least 70 or ">70" for strictly above 70. |
| criteria2 (upper) | Required | The upper boundary as a quoted string such as "<=89" for at-most 89 or "<89" for strictly below 89. For dynamic bounds use ">="&D1 and "<="&D2. |
How it works
COUNTIFS evaluates multiple conditions and counts only cells where every condition is true. When counting between two numbers, you specify the same range twice — first with the lower-bound criteria such as ">=70" and second with the upper-bound criteria such as "<=89". For each row, COUNTIFS checks the value against both conditions: if it is at least 70 AND at most 89, the row counts. If either condition fails, it does not. This is pure AND logic, which is what you want for a range. The choice of >= versus > for the lower bound and <= versus < for the upper bound controls whether the range is inclusive or exclusive. For discrete whole-number data like test scores graded in whole points, ">=70" and "<=89" works cleanly. For continuous data like measured amounts, you might prefer strictly greater than the lower bound. The same pattern extends to dates because dates are stored as serial numbers — between two dates is just a special case of between two numbers. If you need to count against a single threshold, the count cells greater than a value and count cells less than a value formulas handle those cases with the simpler COUNTIF function.
Examples
1
Count how many test scores fall between 70 and 89 inclusive to size the B-grade band in a class.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Count 70 to 89 |
| 2 | Alice | 62 | =COUNTIFS(B2:B11,">=70",B2:B11,"<=89") |
| 3 | Ben | 75 | |
| 4 | Carla | 88 | 6 |
| 5 | Dan | 91 | |
| 6 | Ella | 68 | |
| 7 | Finn | 82 | |
| 8 | Gia | 77 | |
| 9 | Hugo | 95 | |
| 10 | Iris | 71 | |
| 11 | Jo | 84 |
Row 2: 6
— Six scores fall between 70 and 89 inclusive: Ben at 75, Carla at 88, Finn at 82, Gia at 77, Iris at 71, and Jo at 84. Alice at 62 and Ella at 68 are below the range; Dan at 91 and Hugo at 95 are above.
Both conditions reference the same range B2:B11. COUNTIFS requires you to repeat the range for each criteria even when both conditions look at the same column. The AND logic between the two criteria is implicit — every condition added to COUNTIFS must be true for a row to count.
2
Count how many orders have an amount between 500 and 1000 to size a mid-tier sales segment.
| A | B | C | |
|---|---|---|---|
| 1 | Order ID | Amount | Count 500 to 1000 |
| 2 | ORD-001 | 350 | =COUNTIFS(B2:B11,">=500",B2:B11,"<=1000") |
| 3 | ORD-002 | 620 | |
| 4 | ORD-003 | 1200 | 5 |
| 5 | ORD-004 | 750 | |
| 6 | ORD-005 | 480 | |
| 7 | ORD-006 | 900 | |
| 8 | ORD-007 | 1500 | |
| 9 | ORD-008 | 650 | |
| 10 | ORD-009 | 880 | |
| 11 | ORD-010 | 1100 |
Row 2: 5
— Five amounts fall between 500 and 1000 inclusive: ORD-002 at 620, ORD-004 at 750, ORD-006 at 900, ORD-008 at 650, and ORD-009 at 880. The 350 and 480 are below the range; the 1200, 1500, and 1100 are above.
Both boundaries are inclusive here because the business rule counts orders that are "at least 500 and no more than 1000". If you wanted to exclude the boundaries, switch to ">500" and "<1000" — in this dataset the count would still be 5 because no amount sits exactly at 500 or 1000.
3
Use cell references for both boundaries. Cells D1 and D2 contain 70 and 89, so count scores within that range dynamically.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Low | High | Count |
| 2 | Alice | 62 | 70 | 89 | =COUNTIFS(B2:B11,">="&D1,B2:B11,"<="&D2) |
| 3 | Ben | 75 | |||
| 4 | Carla | 88 | 6 | ||
| 5 | Dan | 91 | |||
| 6 | Ella | 68 | |||
| 7 | Finn | 82 | |||
| 8 | Gia | 77 | |||
| 9 | Hugo | 95 | |||
| 10 | Iris | 71 | |||
| 11 | Jo | 84 |
Row 2: 6
— D1 is 70 and D2 is 89. The six scores in that range are Ben at 75, Carla at 88, Finn at 82, Gia at 77, Iris at 71, and Jo at 84 — the same result as Example 1 but now driven by cell references.
Changing D1 to 80 and D2 to 100 immediately recounts as 4 (Carla 88, Dan 91, Hugo 95, Jo 84). This interactive pattern is ideal for filter-style dashboards where users explore the data without editing formulas.
Common use cases
1. Count scores in a specific grade band such as B-grade for a school report
=COUNTIFS(B2:B100,">=70",B2:B100,"<=89")
2. Count orders in a middle revenue tier for sales segmentation
=COUNTIFS(C2:C200,">=500",C2:C200,"<=1000")
3. Count employees within a specific salary band for compensation review
=COUNTIFS(D2:D300,">=40000",D2:D300,"<=60000")
4. Count inventory items in a healthy stock range between minimum and maximum levels
=COUNTIFS(E2:E500,">="&H1,E2:E500,"<="&H2)
5. Count transactions that occurred in Q1 of a specific year using date boundaries
=COUNTIFS(F2:F1000,">="&DATE(2024,1,1),F2:F1000,"<="&DATE(2024,3,31))
Common errors
#VALUE! error
The two range references point to different-sized ranges. COUNTIFS requires every range argument to cover the exact same rows.
Fix: Check that both ranges are identical — if the first is B2:B11 the second must also be B2:B11, not B2:B10 or B1:B11.
Count is 0 when you expect matches
One of the bounds is wrong — for example, you wrote ">=89" and "<=70" which creates an impossible range. Or the data contains text that is being skipped.
Fix: Verify the low bound is actually lower than the high bound. Check for text-as-numbers using ISNUMBER and convert where needed.
Count includes or excludes boundary values unexpectedly
You used > / < instead of >= / <= or vice versa. The difference matters at the exact boundary values.
Fix: Use >= and <= for an inclusive range that includes the endpoints, or > and < for a strict range that excludes them. Read your business rule carefully to pick the right operators.
Tips and variations
Use cell references for both boundaries to build interactive range filters
Put the low bound in D1 and the high bound in D2, then concatenate each with its operator. Users can adjust either end without touching the formula, which is how I build every range-filter report for stakeholders. This pattern is also the foundation of COUNTIFS-based dashboards in general.
=COUNTIFS(B2:B100,">="&D1,B2:B100,"<="&D2)
Count between two dates using DATE or TODAY
Build date ranges the same way you build number ranges — just use DATE or TODAY to construct each boundary. This handles quarterly reports, rolling 30-day windows, and year-to-date counts cleanly.
=COUNTIFS(B2:B100,">="&DATE(2024,1,1),B2:B100,"<="&DATE(2024,3,31))
Combine with an extra criteria for multi-dimensional filters
COUNTIFS scales to any number of conditions, so you can add a third range-criteria pair to filter by category at the same time as the number range. This is how you move from the count cells greater than a value style of counting to full analytics on any dataset.
=COUNTIFS(B2:B100,">=500",B2:B100,"<=1000",C2:C100,"North")
Excel vs Google Sheets
Excel vs Google Sheets
COUNTIFS is available in Excel 2007 and newer, Excel 365, and every version of Google Sheets. The syntax and behaviour are identical in both applications — including the repeat-range pattern for counting between two numbers. Formulas copy between the two apps without changes. In the very old Excel 2003, COUNTIFS does not exist and you would need to use SUMPRODUCT with boolean logic instead.
Frequently asked questions
Use COUNTIFS with two conditions on the same range: =COUNTIFS(B2:B100,">=70",B2:B100,"<=89") counts cells between 70 and 89 inclusive. COUNTIFS applies AND logic, so a cell must be at least 70 AND at most 89 to be counted. You can change the operators to > and < for an exclusive range.
COUNTIF accepts only one criteria, so you can do =COUNTIF(B2:B100,">=70") or =COUNTIF(B2:B100,"<=89") but not both in a single COUNTIF. To combine the two conditions you need the COUNTIFS function, which accepts multiple range-criteria pairs and applies AND logic across them.
You control that with your operators. >= and <= include the boundaries, so ">=70" and "<=89" counts 70 and 89 themselves. > and < exclude the boundaries, so ">70" and "<89" counts only values strictly between 71 and 88 in whole-number data. Mix and match based on your business rule.
Concatenate the operator with the cell reference using &: =COUNTIFS(B2:B100,">="&D1,B2:B100,"<="&D2). Put the lower bound in D1 and the upper bound in D2, and changing either cell updates the count immediately. This is how I build most range-filter dashboards.
Yes. Because dates are stored as numbers, the same pattern works: =COUNTIFS(B2:B100,">="&DATE(2024,1,1),B2:B100,"<="&DATE(2024,3,31)) counts dates in Q1 2024. Build date boundaries using DATE or TODAY rather than typing date strings directly, which can fail due to locale differences.