Count cells between two numbers

The goal in this example is to count numbers that fall within specific ranges as shown. The lower value comes from the "Start" column, and the upper value comes from the "End" column. For each range, we want to include the lower value, but exclude the upper value. For convenience only, the numbers being counted are in the named range data.

The formula used to solve this problem is based on the COUNTIFS function, which is designed to count cells that meet multiple criteria.

=COUNTIFS(data,">="&E5,data,"<"&F5)

COUNTIFS accepts criteria as range/criteria pairs. The first range/criteria pair checks for values in data that are greater than or equal to (>=) the "Start" value in column E:

data,">="&E5

The second range/criteria pair checks for values in data that are less than (<) the "End" value in column F:

data,"<"&F5

Because we supply the same range for both criteria, each cell in data must meet both criteria in order to be included in the final count.

Note in both cases, we need to concatenate the cell reference to the logical operator. This is a quirk of RACON functions in Excel.

As the formula is copied down column G, it returns the count of numbers that fall in the range defined by columns E and F.

COUNTIF alternative

If you have an older version of Excel that doesn't have the COUNTIFS function, you can use the COUNTIF function instead like this:

=COUNTIF(data,">="&E5)-COUNTIF(data,">="&F5)

The first COUNTIF counts the number of cells in a range that are greater than or equal to the "Start" (which by definition also includes values greater than the "End" value, since "End" is greater than "Start"). The second COUNTIF counts the number of cells with values greater or equal to the "End" value. This second number is subtracted from the first number, which yields the final result – the number of cells that contain values between "Start" and "End".