COUNTIF Less Than Cell Value: Excel Formula

Formulas & functions · Updated November 22, 2026

To count cells in a range that are less than a value stored in another cell, use the formula =COUNTIF(A2:A10, "<"&B1), where A2:A10 is the range to count and B1 holds the comparison value. The key is the ampersand (&) that joins the less-than operator with the cell reference.

Why the Ampersand Is Essential

In Excel, comparison operators like <, >, =, and <> must be inside quotation marks when used in a COUNTIF criteria. If you write ", Excel treats it as literal text and returns 0 because it looks for cells containing the string ""<"&B1 becomes the actual condition <5 (if B1 contains 5). This works for any operator: ">"&B1, ">="&B1, "<="&B1, and "<>"&B1 for not equal.

Step-by-Step: Building the Formula

  1. Assume your data is in column A (e.g., A2:A100) and the threshold value is in cell B1.
  2. Click an empty cell where you want the result, e.g., C1.
  3. Type =COUNTIF(A2:A100, "<"&B1) and press Enter.
  4. Excel counts all numbers in A2:A100 that are strictly less than the value in B1. Text and blank cells are ignored.

For example, if A2:A10 contains {10, 20, 30, 40, 50, 60, 70, 80, 90} and B1 is 45, the formula returns 4 (10, 20, 30, 40).

Using a Hardcoded Number vs. Cell Reference

You can also write =COUNTIF(A2:A10, "<45") to count cells less than 45 directly. The advantage of a cell reference is that you can change the threshold without editing the formula. This is particularly useful in dashboards or when you want to test different cutoff values. To make the reference absolute (so it doesn't change when copying the formula), use $B$1: =COUNTIF(A2:A10, "<"&$B$1).

Common Mistakes and How to Fix Them

  • Missing ampersand: Writing " instead of "<"&B1 yields 0. Always use the ampersand.
  • Using quotes around the whole condition: "<"&B1 is correct; " is not.
  • Counting text or blanks: COUNTIF with a numeric comparison ignores text and blank cells, so you don't need to worry about them.
  • Using a formula that returns a value: You can use a calculation in the reference, e.g., =COUNTIF(A2:A10, "<"&B1-10) to count cells less than B1 minus 10.

Alternatives: COUNTIFS and SUMPRODUCT

If you need to count cells less than a value and also meet another condition, use COUNTIFS. For example, to count values in A2:A10 that are less than B1 and greater than 10, use =COUNTIFS(A2:A10, "<"&B1, A2:A10, ">10"). For more complex criteria, SUMPRODUCT can be used: =SUMPRODUCT(--(A2:A10. The double unary operator (--) converts TRUE/FALSE to 1/0. This works in all Excel versions, but COUNTIF is simpler and faster for a single condition.

Frequently Asked Questions

How do I count cells less than a value in Google Sheets?

The formula is identical: =COUNTIF(A2:A10, "<"&B1). Google Sheets uses the same syntax. If you copy from Excel, it works as-is. The only difference is that in Google Sheets, you might need to use semicolons as argument separators in some locales, but the comma works in most English settings.

Can I count cells less than a date?

Yes. Dates are stored as numbers in Excel. If B1 contains a date, the same formula works: =COUNTIF(A2:A10, "<"&B1). Ensure the cells in A2:A10 are actual dates, not text, or the count will be incorrect.

What if the cell value is text?

If B1 contains text, the comparison becomes lexicographic. For example, "<"&"apple" counts cells that come before "apple" alphabetically. This is rarely useful, but it works. For numeric comparisons, ensure B1 is a number.

Why does my formula return 0 even though I see smaller numbers?

Check if the range includes cells with numbers stored as text. COUNTIF ignores text, so if your numbers are left-aligned (text), they won't be counted. Convert them to numbers using the VALUE function or Text to Columns.

Can I use a cell reference without the ampersand?

No, not directly. You must concatenate the operator with the cell reference. Alternatively, you can use the CONCATENATE function, but the ampersand is shorter and more common.

Performance and Large Data Sets

COUNTIF is highly optimized and works well even on large ranges (e.g., 100,000 rows). However, if you use SUMPRODUCT on entire columns (e.g., A:A), it can slow down because it processes all rows. Prefer limiting the range to the actual data. If you need to count across multiple sheets, use COUNTIF with a 3D reference only if the sheets are identical in structure; otherwise, sum multiple COUNTIFs.

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.