How to count values between two numbers in Excel
Formula (Excel and Google Sheets)
=COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2)Use COUNTIFS with a lower and an upper bound on the same range: =COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2).
- Result on the example
- 5 in
E3 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E3. Change any input and the result updates, or edit the formula to experiment.
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.
=COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2)Result5
B2:B1312 rows × 1 col: 68, 70, 74, 79, …A range: 12 cells in one column.
E170The value in E1.
">="&E1">=70"The & operator joins as text.
E279The value in E2.
"<="&E2"<=79"The & operator joins as text.
COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2)5COUNTIFS: Counts rows that meet every condition.- criteria_range1
B2:B1312 rows × 1 col: 68, 70, 74, 79, … - criteria1
">="&E1">=70" - criteria_range2
B2:B1312 rows × 1 col: 68, 70, 74, 79, … - criteria2
"<="&E2"<=79"
- criteria_range1
In Google Sheets
The same formula works in Google Sheets: =COUNTIFS(B2:B13,">="&E1,B2:B13,"<="&E2). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((B2:B13>=E1)*(B2:B13<=E2)) | 5 | 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) | 5 | Everything from 70 up, minus everything above 79. Works even in very old versions without COUNTIFS. |
| =COUNTIFS(B2:B13,">"&E1,B2:B13,"<"&E2) | 3Different result | 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".
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count values between two numbers
E3, count the scores from the low value in E1 to the high value in E2, both included.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,"<"&E1)+COUNTIF(B2:B13,">"&E2).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.