Exceljet

Quick, clean, and to the point

Count cells between two numbers

Excel formula: Count cells between two numbers
Generic formula 
=COUNTIFS(range,">=X",range,"<=Y")
Explanation 

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, X represents the lower boundary, and Y represents the upper boundary of the numbers you want to count.

In the example, the active cell contains this formula:

=COUNTIFS(C4:C10,">=80",C4:C10,"<=90")

How this formula works

The COUNTIFS function is built to count cells that meet multiple criteria. In this case, because we supply the same range for two criteria, each cell in the range must meet both criteria in order to be counted.

Using COUNTIF instead

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(range,">=X")-COUNTIF(range,">Y")

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

Making the criteria variable

If you want to use a value in another cell as part of the criteria, use the ampersand (&) character to concatenate like this:

=COUNTIF(rng,">"&a1)

If the value in cell a1 is "5", the criteria will be ">5" after concatenation.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step 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. Start building valuable skills with Excel formulas today. Learn more.

I LOVE your site. It helps me frequently as I have no formal training but I'm very logic oriented and this site is put together so very well and has assisted me so very much! You've improved the quality of my life. -Mike
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course