# How to count values between two numbers in Excel

Use COUNTIFS with a lower and an upper bound on the same range: `=COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2)`.

**Excel:** `=COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Student | Score |  | Low | 70 |
| Ava | 68 |  | High | 79 |
| Ben | 70 |  | Students |  |
| Chloe | 74 |  |  |  |
| Dan | 79 |  |  |  |
| Eve | 80 |  |  |  |
| Finn | 91 |  |  |  |
| Gia | 55 |  |  |  |
| Hugo | 77 |  |  |  |
| Iris | 79.5 |  |  |  |
| Jack | 83 |  |  |  |
| Kim | 72 |  |  |  |
| Leo | 69.5 |  |  |  |

Formula in E3: `=COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2)`

Result: 5

## How it works

The two conditions apply to the same range and must both be true: at least 70 **and** at most 79. The operators are in quotes and joined to the cells with `&`, so changing E1 or E2 changes the count.

70 and 79 sit on the bounds and count; 79.5 is above 79 and 69.5 below 70, so they don't. Five students scored in the 70s.

## Other ways

- `=SUMPRODUCT((B2:B13>=E1)*(B2:B13<=E2))`: The same count with the comparisons written out. Use it when a bound needs a calculation per row.
- `=COUNTIF(B2:B13,">="&E1)-COUNTIF(B2:B13,">"&E2)`: Everything from 70 up, minus everything above 79. Works even in very old versions without COUNTIFS.
- `=COUNTIFS(B2:B13,">"&E1,B2:B13,"<"&E2)`: Strictly between: use it when values equal to a bound shouldn't count. (Leaves out the scores of exactly 70 and 79.)

## Common mistakes

- Writing `">=70<=79"` as one criteria counts nothing. Each bound needs its own range-and-criteria pair.
- Scores with decimals fall between whole numbers: if 79.5 should count as 79, round them first or set the upper bound to `"<80"`.

## Questions

**How do I count between two numbers with another condition?**

Add a third pair: `=COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2,C2:C13,"Math")`.

**How do I count values outside a range?**

Add two COUNTIFs: `=COUNTIF(B2:B13,""&E2)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-values-between-two-numbers