Cellwise Try it free

Excel guide · 3 min read

COUNTIFS between two numbers in Excel

Quick answerUse =COUNTIFS(B2:B9,">=70",B2:B9,"<=89") to count values from 70 to 89, including both ends. Put the limits in cells and join them with &, as in =COUNTIFS(B2:B9,">="&F2,B2:B9,"<="&G2), so you can change the band without editing the formula.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1ApplicantTest scoreYears experienceSalary askLowHigh
2Imani824610007089
3Joel67772000
4Marta91255000
5Kenji74564500
6Laila591080000
7Owen88358000
8Priya70670000
9Theo95152000

Download this sheet as an Excel practice file (.xlsx)

Count values between two limits

COUNTIFS checks the same range twice: once for the lower limit and once for the upper. A row counts only when both tests pass. Four applicants scored from 70 to 89.

With the limits in F2 and G2, the operator stays in quotes and the cell is joined with &. Typing ">=F2" inside the quotes would compare against the text F2 and return 0.

=COUNTIFS(B2:B9,">="&F2,B2:B9,"<="&G2)

Result: 4

4 test scores are between 70 and 89: 82, 74, 88 and 70.

Inclusive or exclusive limits

>= and <= include the limits; > and < leave them out. Priya scored exactly 70, so she counts with ">=70" but not with ">70". Decide which you mean and write it in the column heading, because "between 70 and 89" is ambiguous.

For grade bands that must not overlap, use >= for the lower limit and < for the next band's start: 70 to under 80, 80 to under 90.

=COUNTIFS(B2:B9,">70",B2:B9,"<89")

Result: 3

3: Priya's 70 drops out because the limits are now exclusive.

Combine it with another column

Add a third condition on another column. Here: a test score of at least 70 and 3 to 6 years of experience. Each condition narrows the count further.

All the ranges must be the same size (B2:B9 and C2:C9), or Excel returns #VALUE!.

=COUNTIFS(B2:B9,">="&F2,C2:C9,">=3",C2:C9,"<=6")

Result: 4

4 applicants have a score of at least 70 and 3 to 6 years of experience: Imani, Kenji, Owen and Priya.

Add up or average within a band

SUMIFS and AVERAGEIFS use the same conditions, with the range to add or average first. Average salary ask for applicants scoring 70 to 89 tells you what that band expects to be paid.

=AVERAGEIFS(D2:D9,B2:B9,">="&F2,B2:B9,"<="&G2)

Result: 63375

$63,375 is the average ask of the four applicants who scored 70 to 89.

Count every band at once

For a frequency table, list each band's lower limit in a column and use COUNTIFS with the current lower limit and the next one. Or count by subtraction: values at least 70 minus values at least 90 leaves the 70 to 89 band. This trick also works with COUNTIF in very old files.

In Excel 365 you can also use =FREQUENCY(B2:B9,{69,79,89}) to spill counts for every band at once.

=COUNTIF(B2:B9,">="&F2)-COUNTIF(B2:B9,">"&G2)

Result: 4

4 again: 6 scores are 70 or more and 2 are above 89, so 4 fall in between.

Common mistakes

Using one condition like ">70<89"

COUNTIFS needs one condition per limit: range,">70",range,"<89".

Limit cells written inside the quotes

Write ">="&F2, not ">=F2".

Numbers stored as text are skipped

Imported numbers that sit on the left of the cell are text and are not counted. Convert them with the warning icon > Convert to Number.

Overlapping bands double count

Use >= for the lower limit and < for the upper (70 to under 80), so a value of exactly 80 lands in one band only.

FAQ

How do I count cells between two numbers in Excel?

=COUNTIFS(range,">=low",range,"<=high"), for example =COUNTIFS(B2:B9,">=70",B2:B9,"<=89").

How do I count values greater than X and less than Y?

Use > and < instead of >= and <=: =COUNTIFS(B2:B9,">70",B2:B9,"<89") leaves out 70 and 89 themselves.

Can I use COUNTIF with two conditions?

COUNTIF takes one condition. Subtract two COUNTIFs, =COUNTIF(r,">=70")-COUNTIF(r,">89"), or use COUNTIFS, which accepts many.

How do I sum values between two numbers?

=SUMIFS(B2:B9,B2:B9,">=70",B2:B9,"<=89") adds the values in the band; to add a different column, put it first.

Functions used

Related guides

Updated