COUNTIF Less Than in Excel: Formula & Examples

The COUNTIF function in Excel counts cells that meet a single condition, and using it with “less than” lets you count values below a specific number, cell reference, or date. You enter the range to search and the criteria like “<10" or "<"&A1, and Excel returns the count of matching cells. This article explains the syntax, shows worked examples, and covers common pitfalls so you can use COUNTIF with less-than logic confidently.

Formula

COUNTIF takes two arguments: range and criteria. For a less-than condition, the criteria is a text string starting with the less-than operator (<) followed by the threshold value. The general syntax is:

=COUNTIF(range, "<"&value)

Where:

• range – the group of cells to examine (e.g., A2:A100).
• value – the numeric threshold, date, or cell reference that cells must be less than.

The ampersand (&) joins the operator with the value. If you type the number directly, you can write "<10" without the ampersand. When using a cell reference, the ampersand is required: "<"&B1.

Worked Example

Suppose you have test scores in cells A2:A10: 55, 72, 68, 90, 45, 83, 61, 77, 58. You want to count how many scores are less than 65.

1. Select a cell for the result, say B2.
2. Type =COUNTIF(A2:A10,"<65").
3. Press Enter. Excel counts 55, 45, 61, and 58 – four scores below 65.

The result is 4.

Using a Cell Reference for the Threshold

Hardcoding the number works, but using a cell reference makes the formula dynamic. If you put the threshold 65 in cell D1, the formula becomes:

=COUNTIF(A2:A10,"<"&D1)

Now if you change D1 to 70, the count updates automatically. This is especially useful when you want to test multiple cutoff values without rewriting formulas.

Counting Less Than or Equal To

To include the threshold itself, use the <= operator. For example, =COUNTIF(A2:A10,"<=65") would count 55, 72? No – 72 is not <=65. It counts 55, 45, 61, 58, and 65 if 65 were present. The difference matters when your data contains values exactly equal to the cutoff.

Working with Dates and Times

Dates in Excel are stored as numbers (days since 1900-01-01), so less-than logic works the same. To count dates before a specific date, use a date function or a cell with a date:

=COUNTIF(B2:B20,"<"&DATE(2025,1,1)) counts all dates before January 1, 2025. If you have a date in cell E1, use "<"&E1. For times, the same applies – times are fractions of a day, so "<0.5" counts times before noon.

Common Mistakes and Troubleshooting

Several pitfalls trip up users:

• Missing ampersand: "<"&A1 is correct; " treats A1 as text and returns 0.
• Spaces in criteria: "< 10" with a space may not work; keep it tight: "<10".
• Text vs. numbers: If your range contains numbers stored as text, COUNTIF may ignore them. Convert text to numbers using the VALUE function or Text-to-Columns.
• Blank cells: COUNTIF ignores blanks for less-than comparisons, which is usually desired.
• Whole column references: =COUNTIF(A:A,"<10") works, but be aware it counts the entire column, which can slow down large sheets.

Alternatives and Related Functions

If you need multiple conditions, use COUNTIFS. For example, to count values less than 65 but greater than 50: =COUNTIFS(A2:A10,">50",A2:A10,"<65"). For summing instead of counting, use SUMIF with the same criteria syntax. If you need to count based on a comparison to another column, SUMPRODUCT can be used, but COUNTIFS is usually simpler.

Frequently Asked Questions

Can I use COUNTIF with a less-than symbol and a cell reference without the ampersand?

No. Without the ampersand, Excel treats the entire string as text, so " will not evaluate the cell reference. You must use "<"&A1 to concatenate the operator with the cell's value.

How do I count cells less than zero?

Use =COUNTIF(range,"<0"). This counts all negative numbers in the range. If you want to count zeros separately, use =COUNTIF(range,"=0").

Does COUNTIF work with text values less than a certain letter?

Yes, COUNTIF can compare text alphabetically. For example, =COUNTIF(A2:A10," counts cells that start with letters before "m" (a-l, case-insensitive). However, the comparison is based on the first character, so "apple" is less than "banana" because 'a' < 'b'.

Why does my COUNTIF return 0 when the value is clearly less?

Check if the criteria is formatted correctly. If you used a cell reference without the ampersand, or if the range contains text-formatted numbers, the count will be 0. Also ensure the operator is a less-than sign (<), not a less-than-or-equal (<=) unless intended.

Can I use COUNTIF with a less-than condition on a date stored as text?

If dates are stored as text, Excel cannot compare them numerically. Convert them to real dates using DATEVALUE or the Text-to-Columns wizard, then apply COUNTIF with the date criteria.

Is there a way to count cells less than a value in another sheet?

Yes, reference the other sheet in the range, e.g., =COUNTIF(Sheet2!A1:A100,"<"&B1). The criteria can reference a cell on the current sheet or the other sheet.

Related

• What Is a Cell Reference? Excel & Sheets Guide
• Standalone vs Embedded Charts: Key Differences
• Unhide Chat in Viber: Step-by-Step Guide

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2