Exceljet

Quick, clean, and to the point

Count cells between two numbers

Excel formula: Count cells between two numbers
Generic formula 
=COUNTIFS(range,">="&A1,range,"<"&B1)
Summary 

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:

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

where data is the named range C5:C15.

Explanation 

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".

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.