Excel
Word
PowerPoint
Outlook
Google Sheets
Google Docs
Google Slides

Learning Hub › Formulas › Count

Count Cells Less Than a Value in Excel and Google Sheets

Count 📊 All versions ✓ Google Sheets Same syntax in both apps

Purpose

Counting values below a threshold is the mirror image of counting values above one, and it shows up constantly in…

Counting values below a threshold is the mirror image of counting values above one, and it shows up constantly in operational reporting. How many stock items are below the reorder point? How many deliveries came in under three days? How many employees took fewer than the allotted sick days this quarter? Every below-target, below-budget, below-limit question uses the same pattern. The COUNTIF function handles it by putting the < operator together with the threshold inside a quoted string. The syntax is symmetrical to the count cells greater than a value formula and the two pair up beautifully to build the count cells between two numbers pattern for range checks. Once you have both greater-than and less-than counting in your toolkit — together with the count cells equal to a value formula for exact matches — almost every single-range numeric summary you will ever need becomes a one-line formula. This is a core pattern worth memorising.

Syntax

✓ All versions ✓ Google Sheets = Same syntax

=COUNTIF(range, "<value")

Arguments

Argument	Required	Description
range	Required	The cells to examine. A specific range like B2:B100 or a column reference like B:B both work. Non-numeric cells are automatically ignored by the comparison.
criteria	Required	The comparison written as a quoted string, such as "<20" for less than 20. For a dynamic threshold use "<"&D1 which joins the operator with the value in D1. For dates use "<"&DATE(2024,1,1) or "<"&TODAY().

How it works

COUNTIF scans the range and compares each numeric cell to the threshold using the < operator, counting only the cells whose value is strictly less than the threshold. The operator and the threshold go together inside one pair of double quotes — "<20" works, but splitting the operator outside the quotes produces a syntax error. The comparison is strict, so a cell that exactly equals the threshold is not included in the count. If you want to include the boundary use "<=" instead, which covers less than or equal to. Blank cells and cells that contain text are silently skipped because comparison operators only evaluate numbers — this is almost always the behaviour you want, but it can surprise you when a column you thought was pure numbers contains a few text entries. For dates, the same formula works because dates are stored as sequential numbers internally, so a date before your threshold has a smaller serial number and is counted. When the threshold should come from another cell rather than being hard-coded, concatenate the < operator with the cell using & — exactly the same pattern as in the count cells greater than a value formula.

Examples

Count how many stock items have a quantity below 20 to identify products needing reorder.

fx =COUNTIF(B2:B11,"<20")

	A	B	C
1	Product	Stock	Count Below 20
2	Mouse	15	=COUNTIF(B2:B11,"<20")
3	Keyboard	45
4	Monitor	8	5
5	Bag	32
6	Cable	18
7	Webcam	60
8	Stand	12
9	Hub	25
10	Pad	5
11	Headset	40

Row 2: 5 — Five products have stock below 20: Mouse at 15, Monitor at 8, Cable at 18, Stand at 12, and Pad at 5. The other five items have 25 or more in stock.

If you instead used "<=20" you would get the same answer here because no product has exactly 20 in stock. In a dataset where a product has exactly 20 units, < excludes it and <= includes it — a one-unit difference that can matter in reorder decisions.

Count how many deliveries arrived in fewer than 3 days to measure fast-delivery performance.

fx =COUNTIF(B2:B11,"<3")

	A	B	C
1	Delivery	Days	Count Under 3
2	DEL-001	2	=COUNTIF(B2:B11,"<3")
3	DEL-002	5
4	DEL-003	1	4
5	DEL-004	7
6	DEL-005	3
7	DEL-006	4
8	DEL-007	2
9	DEL-008	6
10	DEL-009	1
11	DEL-010	8

Row 2: 4 — Four deliveries arrived in fewer than 3 days: DEL-001 at 2 days, DEL-003 at 1 day, DEL-007 at 2 days, and DEL-009 at 1 day. DEL-005 at exactly 3 days is not counted because < is strict.

If the business rule is "3 days or less", change the criteria to "<=3" and DEL-005 gets included, bringing the count to 5. Small operator choice, meaningful impact on reporting.

Use a cell reference for the threshold. Cell D1 contains 30, so count stock items below that value.

fx =COUNTIF(B2:B11,"<"&D1)

	A	B	C	D
1	Product	Stock	Threshold	Count
2	Mouse	15	30	=COUNTIF(B2:B11,"<"&D1)
3	Keyboard	45
4	Monitor	8		6
5	Bag	32
6	Cable	18
7	Webcam	60
8	Stand	12
9	Hub	25
10	Pad	5
11	Headset	40

Row 2: 6 — Cell D1 contains 30. Six products have stock below 30: Mouse at 15, Monitor at 8, Cable at 18, Stand at 12, Hub at 25, and Pad at 5.

The & symbol joins the < operator with the value from D1 to form "<30". Change D1 to 10 and the count drops to 3; change it to 50 and the count rises to 9. This is how you build reorder dashboards where users can adjust the threshold without touching the formula.

Common use cases

1. Count products below reorder level for stock reporting

=COUNTIF(B2:B100,"<20")

2. Count employees who took fewer than the allotted leave days

=COUNTIF(C2:C200,"<15")

3. Count deliveries that arrived in under three days for supplier performance

=COUNTIF(D2:D500,"<3")

4. Count orders with a discount below a target using a cell reference

=COUNTIF(E2:E300,"<"&H1)

5. Count dates before a cutoff to identify aged items in a backlog

=COUNTIF(F2:F1000,"<"&DATE(2023,1,1))

Common errors

Syntax error or unexpected text output

The < operator is outside the quotes or the criteria is unquoted. Excel does not recognise the input as valid criteria.

Fix: Write the full comparison as one quoted string, such as "<20". The operator and value must be inside the same pair of double quotes.

Count is lower than expected

The range contains text or blank cells that COUNTIF silently ignores when using comparison operators.

Fix: Check your data type with =ISNUMBER(B2). If some cells are text-numbers, convert them using VALUE or by multiplying by 1 to make them numeric.

Boundary value was excluded when you wanted it counted

The < operator is strict and excludes cells that exactly equal the threshold.

Fix: Use "<=" instead to include the boundary. Always match your operator to whether the business rule says "less than" or "up to".

Tips and variations

Reference a cell for the threshold instead of hard-coding a number

Put the threshold in a cell like D1 and use "<"&D1 as the criteria. Changing D1 instantly updates every count that uses it — this is how I build interactive low-stock or underperformance dashboards using the COUNTIF function.

=COUNTIF(B2:B100,"<"&D1)

Use <= to include the boundary value

When your rule is "20 or fewer" rather than "fewer than 20", change < to <=. In inventory and threshold work this distinction can change how many items trigger a reorder, so always read your specification carefully.

=COUNTIF(B2:B100,"<=20")

Count old dates by comparing to TODAY

To find items older than a specific point in time, build the threshold from DATE or TODAY. Pair this with the count cells greater than a value pattern to filter date ranges from both ends.

=COUNTIF(B2:B100,"<"&TODAY()-30)

Excel vs Google Sheets

⚡

Excel vs Google Sheets

COUNTIF with comparison operators works identically in Excel 2003 and newer, Excel 365, and every version of Google Sheets. The < and <= operators use the same syntax and behaviour across both applications, and formulas copy between them without modification.