To count values less than 1 in Excel, use =COUNTIF(A2:A100,"<1") when the values are in A2:A100. It counts qualifying entries such as 0.5, 0, and negative numbers, but does not count 1 itself.
The COUNTIF formula and what it means
COUNTIF checks a range and returns the number of cells that satisfy one condition. Its syntax is COUNTIF(range, criteria): the range is the cells to inspect, and the criteria tells Excel what should qualify. For a less-than comparison, put the operator and limit together in quotation marks, as in “<1”.
The quotation marks matter because the criteria is supplied as text, even though Excel interprets it as a numeric comparison. The less-than sign is strict: a value must be smaller than 1. A cell containing exactly 1 is excluded. If you want to include 1, use a less-than-or-equal-to criterion instead.
COUNTIF is available in Excel for Microsoft 365 on Windows, as well as current Excel for Mac and Excel for the web. The formula works the same way in those versions. Depending on your regional settings, Excel may use semicolons instead of commas between function arguments; use the separator Excel expects in your installation.
Which values are less than 1?
Excel compares the stored value, not how the cell looks on the screen. The usual numeric cases are straightforward:
- 0.25 is less than 1, so it is counted.
- 0 is less than 1, so it is counted.
- -3 is less than 1, so it is counted.
- 1 is not less than 1, so it is not counted.
- 1.2 is not less than 1, so it is not counted.
This is a common source of surprises: “less than 1” does not mean “between 0 and 1.” Negative numbers meet the condition too. If the task is to count values from 0 up to, but not including, 1, add a second condition. The lower limit can be inclusive while the upper limit remains exclusive.
Text that resembles a number is not necessarily stored as a number. For example, a cell may contain the text “0.5” rather than the numeric value 0.5. If results look wrong, check how the values are stored and convert numeric-looking text to numbers before relying on a numeric comparison.
Worked example: count fractions, zero, and negatives
Suppose A2:A8 contains 0.4, 1, 0, -2, 0.99, 1.5, and 3. With those values in A2:A8, this formula returns 4: =COUNTIF(A2:A8,"<1"). The four matches are 0.4, 0, -2, and 0.99. The values 1, 1.5, and 3 do not meet the strict less-than condition.
For another useful variation, suppose A2:A8 contains measurements and you want only those from 0 inclusive to 1 exclusive. With the measurements in A2:A8, use =COUNTIFS(A2:A8,">=0",A2:A8,"<1"). In the example above, the result is 3: 0.4, 0, and 0.99 qualify, while -2 is excluded by the lower limit.
COUNTIFS applies multiple conditions, and each condition pair uses a range and a criterion. For an interval like this, repeat the same range for each test. The ranges in a COUNTIFS formula should have matching dimensions, so do not pair A2:A100 for one condition with B2:B50 for another.
Blanks, text, and errors in the range
If the source range may contain empty cells and you need to make the intention explicit, add a nonblank condition. With the data in A2:A100, this formula counts cells below 1 while also requiring the cell to be nonblank: =COUNTIFS(A2:A100,"<1",A2:A100,"<>"). This is especially useful when a report should count only populated records and the range includes unused rows.
COUNTIF is not a data-cleaning function. It will not repair numbers stored as text, remove stray spaces, or decide whether an entry such as “n/a” should count. Inspect suspicious cells by selecting them and checking the formula bar, or test a small sample in a temporary area. Convert text values to numbers when they represent numeric data, and keep labels or missing-value markers separate from the numeric field where possible.
Also consider errors in the source data. If a range contains values such as #N/A or #VALUE!, resolve or handle those errors as appropriate for the workbook rather than assuming they are meaningful numbers. A count can be mathematically correct for the values it evaluates while still being misleading if the source column is inconsistent.
Percentages, times, and dates
Percentages in Excel are stored as decimal values. A displayed 25% is stored as 0.25, so it is less than the numeric value 1. In percentage formatting, 1 represents 100%, not 1%. To count percentages below 1%, use a 1% threshold. With percentage values in A2:A100, =COUNTIF(A2:A100,"<1%") counts values below 0.01, or below one percent.
Formatting changes how a value is displayed, not the comparison itself. A cell displayed as 50% and a cell displayed as 0.5 contain the same underlying value for this purpose. If your result seems unexpectedly large, check whether you intended a threshold of 1 (100%) or 1% (0.01).
Times are also stored as fractions of a day. For instance, 6:00 AM is 0.25 of a day, so a time-only value is less than 1 and meets the criterion. Dates, by contrast, are represented by serial numbers in Excel; ordinary modern dates have values greater than 1. A less-than-1 test on a column that mixes dates and times may therefore count time fractions but not ordinary date values. Choose a threshold that reflects the kind of data, rather than relying on the cell’s display format.
Use a limit stored in another cell
A fixed limit is convenient for a one-off count, but a cell-based limit is easier to change in a worksheet or report. Suppose A2:A100 contains the values and D1 contains the numeric threshold 1. With that layout, use =COUNTIF(A2:A100,"<"&D1). Excel joins the less-than operator to the value in D1 to form the comparison criterion.
Because D1 contains the limit, changing it to 0.5 updates the count without editing the formula. Make sure D1 contains a number rather than text that only looks numeric. If you want the threshold to be visible and self-explanatory, label the cell—for example, “Limit”—and format it suitably for the data. The criterion remains strict, so a value equal to the limit is excluded.
Common mistakes and quick checks
- Leaving out the quotation marks: the comparison criterion should be entered as text, such as “<1”, not as an unquoted operator and number.
- Using the wrong boundary: “<1” excludes 1. Use a less-than-or-equal-to condition if 1 should qualify.
- Forgetting negative values: negative numbers are below 1. Add a lower-bound condition when you mean a range starting at zero.
- Confusing 1 with 1%: the numeric value 1 is equivalent to 100% when displayed as a percentage. One percent is 0.01.
- Counting a different range: check that the range in the formula covers the intended records, including the first and last data rows.
- Assuming the filter controls COUNTIF: COUNTIF evaluates its specified range; filtering rows does not change the function into a visible-rows-only count.
A reliable way to troubleshoot is to test a short range with known values, including a value below 1, exactly 1, and a negative number. If that small test behaves as expected but the full count does not, inspect the larger range for text numbers, blanks, unexpected values, or an incorrect endpoint.
When to use COUNTIFS or another approach
Use COUNTIF when there is one condition, such as “less than 1.” Use COUNTIFS when the rule has multiple conditions, such as “at least 0 and less than 1,” or “less than 1 and in a particular category.” Keeping each condition visible in the formula makes the logic easier to audit than combining several unrelated tests in one complicated expression.
If the goal is to display which entries qualify rather than count them, a filter or a helper column may be more useful. A filter lets you inspect matching records, while a helper column can label each row for later review. COUNTIF is best when the desired output is a single count and the condition is clear. It does not show which cells contributed to the result, so retain the source data or use a review method if the count needs to be checked.
Frequently Asked Questions
Does COUNTIF less than 1 include zero?
Yes. Zero is less than 1, so it meets the condition. Negative numbers meet it as well. If you want positive values only, add a lower limit greater than zero.
How do I count values less than or equal to 1?
Use a less-than-or-equal-to criterion instead of a strict less-than criterion. That includes values exactly equal to 1 as well as values below it.
How do I count values between 0 and 1?
Use COUNTIFS with two conditions on the same range: a lower bound of 0 and an upper bound of 1. Decide whether each boundary should be inclusive, since that changes which edge values qualify.
Why does a cell displayed as 50% count as less than 1?
Excel stores 50% as 0.5, and 0.5 is less than 1. If you meant values below 1%, use a 1% threshold rather than a threshold of 1.
Can COUNTIF count only visible rows?
No. COUNTIF evaluates the cells in its range, including rows hidden by a filter. If the count must respond to filtered visibility, use a method designed for visible rows rather than assuming COUNTIF will ignore hidden entries.
Related
- How to Close the Queries & Connections Pane in Excel
- Cleaning Checklist Template Excel: Setup and Tips for Teams
- Close and Load in Excel: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Gtt per Min Formula: Examples and Safe CalculationThe gtt per min formula is gtt/min = (mL × drop factor in gtt/mL) ÷ time in minutes.…
- Excel Timesheet Calculator Formula: Examples and TipsFor an Excel timesheet with start time in B2, end time in C2, and an unpaid break duration…
- IF Then for Dates in Excel: Formulas and ExamplesTo use IF with dates in Excel, compare a cell containing a real date with another date or…
- CHAR Excel Usage Examples: Formulas, Codes, and TipsIn Excel, CHAR converts a number from the computer’s character set into a character. For example, with no…
- Excel Day Name from Date: Formulas, Formats, and FixesTo show the day name for a date in Excel, enter =TEXT(A2,"dddd") in another cell, assuming A2 contains…
- Excel Formula to Add Dates in a Weekly Sequence AutomaticallyTo add dates in weekly sequence in Excel, enter a starting date in A2, then put =A2+7 in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.