To count values below a number in Excel, use =COUNTIF(A2:A20,"<10"). Replace A2:A20 with your range and 10 with the limit you want; COUNTIF counts values strictly less than that limit, not values equal to it.
The Basic COUNTIF Formula
COUNTIF checks a range for cells that meet one condition, then returns how many match. Its syntax is =COUNTIF(range,criteria). The range is the cells to inspect, and the criteria describes what qualifies. For a less-than test, the criteria combines the less-than operator with the cutoff number inside quotation marks.
For example, suppose A2:A9 contains 4, 8, 10, 12, a blank, 0, -2, and the text pending. With those values in that order, the formula =COUNTIF(A2:A9,"<10") returns 4: it counts 4, 8, 0, and -2. The value 10 is excluded because the operator is strictly less than, while the blank and text entry do not qualify as numbers below 10.
COUNTIF is useful for a quick tally: scores below a pass mark, orders under a quantity threshold, days with fewer than a target number of visits, or readings below a limit. It returns a count, not a list of the matching cells or their values.
How the Less-Than Criterion Works
In a COUNTIF formula, comparison operators are part of the criteria text. The common operators are less than (<), less than or equal to (<=), greater than (>), greater than or equal to (>=), and not equal to (<>). The distinction matters at the boundary: if 10 is the cutoff, <10 excludes 10, while <=10 includes it.
When the cutoff is a fixed number, put the operator and number together inside quotation marks, as in "<10". A common mistake is to write the operator outside the quotes or to leave the criterion as an unquoted expression. COUNTIF expects its criterion argument in a form Excel can interpret as a condition.
Another useful point is that COUNTIF is not case-sensitive when matching text. That is usually irrelevant to a numeric less-than test, but it can matter if you are also counting text categories. If the goal is to find values below or equal to a threshold, use the two-character operator <= rather than changing the number.
Use a Cell for the Cutoff
If the threshold may change, keep it in a worksheet cell instead of typing it into the formula. The criteria still needs to be text, so join the operator in quotation marks to the cell reference with the ampersand (&). With values in A2:A20 and the cutoff in D1, use =COUNTIF(A2:A20,"<"&D1). If D1 contains 10, this applies the same test as a fixed cutoff of 10.
For a concrete result, suppose A2:A6 contains 3, 8, 10, 11, and 2, while D1 contains 9. The formula returns 3 because 3, 8, and 2 are below 9. If you change D1 to 10, the result becomes 3 again in this example; if you change it to 11, the result becomes 4 because 10 now qualifies. The formula updates when the threshold cell changes.
When copying the formula down a column, lock the threshold reference if it should remain fixed. With data in A2:A20 and a shared cutoff in D1, the formula =COUNTIF(A2:A20,"<"&$D$1) keeps pointing to D1 when copied. The dollar signs make the reference absolute; they do not change the comparison itself. You can also select a cell reference and press F4 in Excel for Windows to cycle through reference styles. On Mac, the shortcut may depend on keyboard settings; typing the dollar signs directly works in either version.
Worked Examples for Common Tasks
These examples show how to adapt the criterion while keeping the same basic pattern:
- Count scores below 60: With scores in B2:B50,
=COUNTIF(B2:B50,"<60")counts the scores under 60. A score of exactly 60 is not included. - Count quantities under a changeable target: With quantities in C2:C30 and the target in F1,
=COUNTIF(C2:C30,"<"&F1)uses the current value in F1 as the cutoff. - Count amounts at or below zero: With amounts in D2:D100,
=COUNTIF(D2:D100,"<=0")counts zeros and negative numbers. Use this only if zero should count as meeting the condition.
For the first example, imagine B2:B6 contains 55, 60, 45, 72, and 59. The formula returns 3, counting 55, 45, and 59. This simple check is a useful way to confirm whether a result makes sense: list which values meet the boundary, then compare that tally with the formula.
Counting Dates Before a Deadline
Excel stores valid dates as serial numbers, so a less-than criterion can count dates earlier than a cutoff. The cells must contain real Excel dates rather than text that merely looks like a date. With dates in B2:B5 and the deadline in D1, where B2:B5 contain December 31, 2024; January 1, 2025; January 2, 2025; and a blank, and D1 contains January 1, 2025, use =COUNTIF(B2:B5,"<"&D1). It returns 1 because only December 31 is earlier; January 1 itself is not before the deadline.
Enter the dates using a format Excel recognizes, and format the cells as dates if you want them displayed clearly. Regional date settings can affect how typed date strings are interpreted, so a date that appears correct may still be stored as text. If the count is unexpectedly zero, check whether the entries are actual dates and whether the cutoff is the date you intend. For a timestamp deadline, remember that a date with a time includes a time component too; a value at midnight may compare differently from one later that same day.
When to Use COUNTIFS Instead
COUNTIF handles one condition. To require a value below a limit and another condition at the same time, use COUNTIFS, which supports multiple criteria. For example, suppose B2:B5 contains scores 6, 9, 9, and 4; C2:C5 contains Complete, Complete, Open, and Complete; and E1 contains 8. With those cells, =COUNTIFS(B2:B5,"<"&E1,C2:C5,"Complete") returns 2: the completed rows with scores 6 and 4 qualify. The row ranges in COUNTIFS should have the same dimensions so Excel can evaluate corresponding records together.
Use COUNTIFS when conditions must all be true, such as counting completed jobs with a score below a threshold. Do not add separate COUNTIF results if you need the conditions to apply to the same row: separate counts cannot establish that the low score and matching status belong to the same record. If you need an either/or test, such as values below 10 or above 90, the approach is different; you can add two COUNTIF results when the conditions cannot overlap, or use a more tailored method when they can.
Common Problems and Quick Checks
If a formula returns zero or a count that seems wrong, check the criterion, the data type, and the selected range before assuming COUNTIF is broken. These are frequent causes:
- Missing quotation marks: A fixed comparison should be written as criteria text, such as
"<10". - Missing ampersand: When the limit is in a cell, join it to the operator with
&, as in"<"&D1. - Wrong boundary:
<10does not include 10. Choose<=10if equality should count. - Numbers stored as text: Imported data may look numeric but be stored as text. Check representative cells and convert the values to numbers if the data is intended to be numeric.
- Unexpected blanks: A blank is not a numeric value below a positive cutoff for this use. If blanks should be counted separately, test for blanks with an appropriate blank criterion rather than treating them as low numbers.
- Range mismatch: Make sure the formula covers the intended rows and does not omit new records or include headers.
Also check the argument separator if Excel reports a formula error. The examples here use commas, as in =COUNTIF(A2:A20,"<10"). Some regional settings use semicolons between function arguments instead. Excel usually follows the separator configured for the system, so use the one accepted by your installation. The operator itself remains inside quotation marks either way.
Choosing Between COUNTIF and Other Options
COUNTIF is the simplest choice when the task is to count cells in one range that meet one less-than condition. Choose COUNTIFS when the count must satisfy multiple conditions at once. If you instead need to see the matching rows, a count formula is not enough; use Excel’s filter tools or a dynamic array approach available in Microsoft 365 and supported newer Excel versions. Filtering is often easier for a one-time review, while a formula is useful when the count should update as values change.
For a quick visual scan rather than a numeric total, Home > Conditional Formatting > Highlight Cells Rules > Less Than can highlight values under a chosen number. This does not replace COUNTIF: it formats qualifying cells instead of returning a count. On Excel for Mac and Excel for the web, the available commands and dialog details can differ slightly, but COUNTIF formulas work in those versions too. On Windows, the path above is available in Excel for Microsoft 365.
COUNTIF is also not a substitute for data validation. If users should be prevented from entering values below a minimum, set a validation rule rather than merely counting existing entries. Counting tells you what is already in the range; it does not stop future invalid entries.
Frequently Asked Questions
How do I count cells less than a value in another cell?
With the data in A2:A20 and the threshold in D1, use =COUNTIF(A2:A20,"<"&D1). The ampersand joins the operator text to the threshold. The result changes if D1 changes.
Does COUNTIF less than include the limit itself?
No. The less-than operator is strict, so a value equal to the cutoff is excluded. Use <= in the criterion when you want to include values equal to the limit.
Can COUNTIF count dates before a date?
Yes. Excel dates are stored as numbers, so a less-than condition works with real date values. Keep the date cutoff in a cell and join it to the operator, or enter a suitable date criterion; verify that the cells are dates rather than text.
Why does COUNTIF return zero for values that look smaller?
The entries may be numbers stored as text, the formula may reference the wrong range, or the criterion may be written incorrectly. Check the stored data type, the exact cutoff, and whether you intended less than or less than or equal to.
How do I count values below a limit and matching a status?
Use COUNTIFS, with one condition for the numeric range and another for the status range. For example, the formula in the COUNTIFS section counts rows where the score is below E1 and the status is Complete.
A Reliable Way to Build the Formula
Start by identifying the range to count and deciding whether equality belongs in the result. Write a fixed limit as a quoted criterion, or place a variable limit in a cell and concatenate it with the operator. Then test the formula against a small set of known values, including one below the cutoff, one equal to it, and one above it. That boundary check catches many mistakes quickly.
For a reusable worksheet, label the threshold cell and consider making the data range an Excel Table so added rows are easier to include in ongoing analysis. Keep the formula’s purpose clear with a nearby label such as Values below target. COUNTIF is intentionally straightforward: once the range and strict-versus-inclusive boundary are correct, Excel recalculates the tally as the underlying values or threshold change.
Related
- Excel Data Model Calculated Field: What to Use Instead
- Excel Data Model: How to Combine Tables and Analyze Them
- Excel Data Model Measures: Add, Format, and Use One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Concatenate Excel Formula With Comma: ExamplesTo concatenate two cells with a comma and a space between them, use =A2&", "&B2. For example, if…
- Excel SUMIFS With One Criterion: Formula and ExamplesTo sum values in Excel when one condition is true, use SUMIFS with one criteria range and one…
- Excel Formula for Budget Amount RemainingIf your total budget is in B2 and the amount spent is in C2, enter =B2-C2 in another…
- COUNTIF in Excel Between Two Values: The Right FormulaTo count numbers between two limits in Excel, use COUNTIFS, which applies both limits to the same range.…
- Excel Formula for Quarterly Dates: Examples and TipsTo calculate the first day of the calendar quarter containing a date in A2, use =DATE(YEAR(A2),3*INT((MONTH(A2)-1)/3)+1,1). For example,…
- HLOOKUP Match in Excel: Exact and Approximate ResultsTo match a value across the top row of a range in Excel, use HLOOKUP and set its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.