Quick, clean, and to the point

Count cells less than

Excel formula: Count cells less than
Generic formula 

To count the number of cells that contain values less than a particular number, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells that contain numbers, and X represents the boundary below which you want to count.

In the example, the active cell contains this formula:


How this formula works

COUNTIF counts the number of cells in the range that contain numeric values less than X and returns the result as a number.

If you want to count cells that are less than or equal to 80, use:


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


If the value in cell a1 is "100", the criteria will be "<100" after concatenation.

Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this accelerated video course, 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 powerful skills to troubleshoot, trace errors, and fix problems. This is the formula training you should have had to begin with. See details here.

I love to use excel and use it a lot in my work. Your tips, tricks and examples have helped me enormously. - Lisa
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course