Exceljet

Quick, clean, and to the point

Count cells less than

Excel formula: Count cells less than
Generic formula 
=COUNTIF(rng,"<x")
Summary 

To count cells that contain values less than a given number, you can use the COUNTIF function. In the generic form of the formula, range is the range of cells to count, and x is the number below which you want to count. In the example shown, cell F5 contains this formula:

=COUNTIF(C5:C16,"<75")

COUNTIF returns 1, since there is one cell in C5:C16 with a value less than 75.

Explanation 

In this example, the goal is to count test scores in column C that are less than 75. The simplest way to do this is with the COUNTIF function, which takes two arguments, range and criteria:

=COUNTIF(range,criteria)

The test scores in the range C5:C16 and we want to count scores less than 75, so we configure COUNTIF like this:

=COUNTIF(C5:C16,"<75") // returns 1

Since there is only one score in C5:C16 that is less than 75, COUNTIF returns 1 as a result. Notice that criteria is given as a text value in double quotes (""). This is a requirement of the the COUNTIF function which is in a group of eight functions that use a special syntax for criteria. In this syntax, logical operators are joined with numeric values and provided as text.

Less than or equal to

To count cells that are less than or equal to, adjust the formula to use "<=" instead of "<". In the example shown, the formula in F6 is:

=COUNTIF(C5:C16,"<=75") // returns 2

Here, COUNTIF returns 2 since there are two scores in C5:C16 less than or equal to 75.

Value in a another cell

To adjust the formula to use a value in another cell as part of the criteria, you can concatenate the logical operator to the cell reference with the ampersand (&) operator like this:

=COUNTIF(range,"<"&A1)

For example, with 75 in cell A1, the criteria will be "<=75" after concatenation:

=COUNTIF(range,"<="&A1)
=COUNTIF(range,"<=75")
=2

The result will again be 2. If the value in A1 is changed to a different number, COUNTIF will return a new result.

COUNTIFS function

The COUNTIFs function is designed to handle multiple criteria, but can be used just like the COUNTIF function in this example:

=COUNTIFS(C5:C16,"<75") // returns 1
=COUNTIFS(C5:C16,"<=75") // returns 2

Video: How to use the COUNTIFS function

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.