Count cells between two numbers
To count the number of cells that contain values between two numbers in a range of cells, you can use the COUNTIFS function. In the generic form of the formula (above), range represents a range of cells that contain numbers, A1 represents the lower boundary, and B1 represents the upper boundary of the numbers you want to count. In the example shown, the formula in G5, copied down, is:
where data is the named range C5:C15.
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 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:
The second range/criteria pair checks for values in data that are less than (<) the "End" value in column F:
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.
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.
If you have an older version of Excel that doesn't have the COUNTIFS function, you can use the COUNTIF function instead like this:
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".