Excel COUNTIF Less Than a Cell: Formula and Examples

Formulas & functions · Updated November 22, 2026

To count values in Excel that are less than a value in another cell, use =COUNTIF(B2:B20,"<"&D2). This assumes the values are in B2:B20 and the comparison value is in D2; the ampersand joins the less-than operator to the cell’s value so COUNTIF can use it as its criterion.

The Basic COUNTIF Formula

COUNTIF checks a range against one condition and returns how many cells meet it. Its structure is =COUNTIF(range,criteria). For a less-than comparison, the criteria must contain the operator and the value to compare against. When that value is in a cell, join the operator in quotation marks to the cell reference with the ampersand (&).

For example, if B2:B20 contains scores and D2 contains a threshold, the criterion is built from the text “<” and the value in D2. If D2 contains 10, Excel evaluates the criterion as less than 10. The comparison is strict: a value equal to 10 is not counted. Use a less-than-or-equal operator if equality should count.

This pattern is useful whenever the cutoff may change, such as counting orders below a target, scores under a pass mark, or dates before a deadline. Instead of editing the formula each time the cutoff changes, update the comparison cell and the result recalculates.

How Excel Builds the Criteria

COUNTIF expects its criteria as a single value or text expression. A literal criterion can be written inside quotation marks, but a cell reference cannot simply be placed inside those quotation marks and treated as a live reference. The ampersand combines separate pieces: the operator text and the value currently stored in the reference cell.

For instance, the expression "<"&D2 becomes a criterion such as “<10” when D2 contains 10. The formula does not compare B2:B20 to the address D2 itself; it compares each cell in the range to D2’s contents. This distinction is the source of many formula errors.

Use straight quotation marks around the operator. Curly quotation marks copied from a word processor are not Excel formula delimiters. Also make sure the ampersand is outside the quoted operator text. A criterion such as "<D2" is literal text and does not dynamically read D2.

Worked Example With a Numeric Cutoff

Suppose B2:B9 contains these values: 12, 8, 15, 6, 10, 4, 13, and 9. Put 10 in D2. With those values in B2:B9 and the cutoff in D2, enter:

=COUNTIF(B2:B9,"<"&D2)

The result is 4. The values below 10 are 8, 6, 4, and 9. The value 10 is excluded because “less than” does not include equality; 12, 15, and 13 are also excluded.

If the cutoff is a fixed number that will not change, a literal criterion is an option. With the values still in B2:B9 and the threshold fixed at 10, use:

=COUNTIF(B2:B9,"<10")

This also returns 4 for the example. The cell-reference version is generally easier to maintain when users will change the threshold, while the literal version is convenient for a one-off count or a fixed rule.

Cell References and Copying the Formula

By default, both the range and the cutoff reference are relative. If you copy a formula down one row, B2:B20 shifts to B3:B21 and D2 shifts to D3. That is useful when each row has its own data range and threshold, but it is usually not what you want when every formula must inspect the same range.

To keep the data range fixed while allowing the cutoff to change by row, use absolute references for the range. With the shared values in B2:B20 and the first threshold in D2, enter this in E2 before filling down:

=COUNTIF($B$2:$B$20,"<"&D2)

The dollar signs lock the range; D2 remains relative. On Windows, select a reference in the formula and press F4 to cycle through reference styles. On a Mac, the equivalent shortcut is typically Command+T in Excel, though keyboard behavior can depend on the keyboard and macOS settings. You can also type the dollar signs directly.

If the cutoff itself should remain fixed as you copy the formula, lock that reference too. With B2:B20 as the data and D2 as the single fixed cutoff, use:

=COUNTIF($B$2:$B$20,"<"&$D$2)

Think about what each copy should do before adding dollar signs. Lock only the parts that are meant to stay put.

Using COUNTIF With Dates and Other Data

Excel stores valid dates as serial numbers, so a less-than comparison works naturally for dates when the cells contain actual Excel date values. With delivery dates in A2:A100 and a deadline in D2, use:

=COUNTIF(A2:A100,"<"&D2)

This counts dates strictly earlier than the deadline. A date equal to D2 is not included. If the cells contain date-looking text rather than real dates, Excel may not compare them as intended. Check the cell format and, more importantly, whether Excel recognizes the entries as dates; changing a display format alone does not always convert text into date values.

For timestamps, a date without a time is treated as midnight at the start of that date. Thus, a timestamp later on the same calendar day is greater than the date value for midnight. If the task is “before the end of this day” rather than “earlier than this exact date-time,” choose a cutoff that reflects that distinction.

COUNTIF can also apply criteria to text, but less-than text comparisons are less intuitive than numeric or date comparisons. Excel’s text matching is not case-sensitive for COUNTIF. For alphabetic ordering, confirm that a text comparison is really the intended rule and test representative values, especially if the data contains mixed text and numbers.

Exclude Blanks or Add More Conditions

A frequent follow-up is whether blank cells should count as values below the cutoff. If blanks must be excluded, use COUNTIFS to require both a value below the cutoff and a nonblank cell. With values in B2:B20 and the cutoff in D2, use:

=COUNTIFS(B2:B20,"<"&D2,B2:B20,"<>")

COUNTIFS applies both conditions to the same range: the cell must be less than D2 and must not be blank. This is clearer than assuming blanks will behave the way you expect in a comparison. COUNTIFS is also the natural choice when the count needs multiple criteria, such as values below a limit for one department only. Each additional condition needs a corresponding criteria range and criterion.

If the cutoff cell might be empty, decide what the result should mean rather than letting an empty input drive an unintended comparison. For example, if an empty D2 should leave the result blank, use this formula with values in B2:B20 and the optional cutoff in D2:

=IF(D2="","",COUNTIF(B2:B20,"<"&D2))

This returns an empty display when D2 is blank and otherwise performs the count. It does not validate that D2 contains a number or date; add a separate input check if the sheet requires a particular type.

Common Errors and How to Fix Them

  • Putting the cell reference inside the quotes: "<D2" is treated as criterion text rather than an operator joined to D2. Close the quotes after the operator, then add the ampersand and reference.
  • Using the wrong comparison sign: < excludes equality. Use <= when values equal to the cutoff should count, and make sure the operator text matches the rule.
  • Comparing numbers stored as text: imported entries may look numeric but be stored as text. Check how the values are stored and convert them if necessary; formatting text to display as a number does not necessarily change its underlying type.
  • Including headers in the range: a header row is not part of the numeric data and can complicate interpretation. Use a range that starts at the first data row, or use a structured table reference if the data is an Excel table.
  • Getting an unexpected count after copying: inspect whether the data range or cutoff moved. Add absolute references to the range, the cutoff, or both as appropriate.
  • Using curly quotation marks or a missing ampersand: retype the formula punctuation directly in Excel and verify that the operator is quoted while the cell reference is outside the quotes.

When troubleshooting, click the formula cell and inspect the colored reference outlines in the worksheet. Confirm the selected range, the value in the cutoff cell, and whether the comparison should include equality. Testing with a small range whose values you can count manually is a reliable way to isolate the problem.

Frequently Asked Questions

How do I count cells less than a value in another cell? Use COUNTIF with the operator joined to the reference, such as =COUNTIF(B2:B20,"<"&D2), where B2:B20 is the range and D2 holds the comparison value.

How do I count values less than or equal to a cell? Change the operator to less than or equal to. With values in B2:B20 and the cutoff in D2, use =COUNTIF(B2:B20,"<="&D2). The result includes cells equal to D2 as well as smaller values.

Can COUNTIF compare a range with a date in another cell? Yes, if the range and cutoff contain real Excel date values. Join the less-than operator to the date cell just as you would with a number. Remember that a strict comparison excludes a date equal to the cutoff.

Why does my formula return zero? Check that the formula uses an ampersand between the quoted operator and the reference, that the reference contains the expected cutoff, and that the data are comparable numbers or dates rather than text. Also verify the range and whether the values are actually below the threshold.

Should I use COUNTIF or COUNTIFS? Use COUNTIF for one condition, such as less than a threshold. Use COUNTIFS when you need two or more conditions, including excluding blanks or counting only rows that also meet another requirement.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.