COUNTIF Between Two Numbers in Excel

To use COUNTIF between two numbers, you actually need its bigger sibling, COUNTIFS, with two conditions on the same range: =COUNTIFS(B2:B8,">=30",B2:B8,"<=45"). One condition sets the lower limit and the other sets the upper limit, and a cell is counted only when it meets both. This guide shows the formula, the difference between including and excluding the limits, limits stored in cells, and how to build a table of bands.

Why COUNTIF alone can't count between two numbers#

COUNTIF takes a single condition, so it can say "30 or more" or "45 or less", but not both at once. Every example below uses this list of ages in A1:B8:

People often try to squeeze both limits into one criteria, like ">=30<=45" or "30-45". Excel reads those as plain text, finds no cell that matches, and returns 0. You can't put AND inside COUNTIF either. You need two separate conditions.

How to count between two numbers with COUNTIFS#

COUNTIFS takes pairs of a range and a criteria. For a between count, use the same range in both pairs:

=COUNTIFS(B2:B8,">=30",B2:B8,"<=45")

1. Click an empty cell, such as D2.
2. Type =COUNTIFS(B2:B8,">=30", for the lower limit.
3. Type B2:B8,"<=45") for the upper limit.
4. Press Enter.

The result is 4: Ben (37), Cara (45), Finn (31) and Gia (40). COUNTIFS uses AND logic, so each age must pass both tests. Ana (24) and Dan (29) fail the first; Eve (52) fails the second.

COUNTIFS works in Excel 2007 and every later version. The COUNTIFS function guide lists its arguments, and COUNTIF vs COUNTIFS explains when you need each one.

Including or excluding the limits#

The operators decide whether the limit numbers themselves are counted:

Cara is exactly 45, so she drops out as soon as the upper test becomes "<45". Ask yourself what should happen to a value right on the line before you write the formula. For bands that sit next to each other, like 30 to 39 and 40 to 49, a common choice is >= for the lower limit and < for the next band's start. That way no value falls into two bands or into none.

How to use limits stored in cells#

Put the limits in cells so you can change them without editing the formula. With 30 in E1 and 45 in F1:

=COUNTIFS(B2:B8,">="&E1,B2:B8,"<="&F1)

The result is still 4. The & joins each operator to the number in the cell, so Excel sees ">=30" and "<=45". Keep the operator inside quotes and the cell reference outside. Writing ">=E1" makes Excel look for the text "E1" and return 0.

Change F1 to 40 and the count becomes 3 (37, 31 and 40). This is the setup to use in a dashboard or any sheet where someone else picks the range.

The subtraction method with COUNTIF#

You can get the same answer with two plain COUNTIFs: count everything at or above the lower limit, then take away everything above the upper limit:

=COUNTIF(B2:B8,">=30")-COUNTIF(B2:B8,">45")

The first part counts 5 ages (37, 45, 52, 31, 40). The second counts 1 (52). The result, 5 - 1, is 4. Note that the second part uses ">45" so that 45 itself stays in the count. This method is handy in very old files, but COUNTIFS is easier to read, so prefer it.

How to count values in bands#

To see how values spread across ranges, build a small band table and point COUNTIFS at each row. With the band starts in E2:E5 and band ends in F2:F5:

Write the formula once in G2, then fill it down (Windows: Ctrl+D; Mac: ⌘D). The $ signs keep the age range fixed while E2 and F2 move to E3 and F3. The counts add up to 7, one for each employee, which is a quick check that no band overlaps or leaves a gap.

The same idea works for summing instead of counting. The guide to COUNTIFS and SUMIFS with multiple conditions shows how.

Between two dates and other variations#

Dates are numbers in Excel, so the between pattern works on them too. Build each date with DATE:

=COUNTIFS(C2:C8,">="&DATE(2024,3,1),C2:C8,"<="&DATE(2024,3,31))

This counts the dates in March 2024. The guide to COUNTIF with dates covers before, after and whole months.

You can also add a third condition on another column. For example, to count employees aged 30 to 45 in the Sales department, add C2:C8,"Sales" as a third pair. And if you need a label on each row instead of a total, such as "In range", see IF between two numbers.

How to count values outside a range#

To count the values outside a range, add two COUNTIFs, one for each side:

=COUNTIF(B2:B8,"<30")+COUNTIF(B2:B8,">45")

That gives 2 (24 and 29) plus 1 (52), so 3. You can't use COUNTIFS for this, because no age can be both below 30 and above 45 at the same time, so it would return 0. A quick check: inside plus outside should equal the total number of values, and 4 + 3 = 7.

Practise it#

Between counts appear often in job tests because they check that you know COUNTIFS takes the same range twice. Practise COUNTIFS and SUMIFS in a live spreadsheet: you type each formula and the lesson checks it instantly. Or try the exercise on this page and count the employees aged 30 to 45.