How to Count Cells Less Than a Value in Google Sheets Using COUNTIF

Monitoring financial budgets, academic grading cut-offs, or warehouse stock thresholds in Google Sheets frequently requires quantifying how many records fall below a critical baseline. For example, a procurement manager may need to count how many warehouse SKUs have fewer than 10 units remaining, or a tutor may need to determine how many test scores dropped below 50. In Google Sheets, the native COUNTIF function evaluates criteria using logical comparison operators to return exact counts instantly.

Understanding COUNTIF Comparison Operator Syntax

The COUNTIF function requires two core arguments: a range of cells to inspect and the evaluation condition:

=COUNTIF(range, criterion)

• range: The continuous or non-continuous block of cells containing your numbers (e.g. B2:B100).
• criterion: The logical test. When using mathematical comparison operators (such as less than < or less than or equal to <=), the condition must be enclosed in quotation marks.

Counting Cells Strictly Less Than a Fixed Value

To count cells with numbers strictly smaller than a given target (for example, identifying inventory items with less than 20 units in column B):

1. Click an empty cell where you want your summary count displayed.
2. Enter the following formula:

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

3. Press Enter.

Google Sheets scans the range and counts only cells whose numeric value is strictly less than 20 (values equal to 20 are excluded).

Using Less Than or Equal To (<=) for Inclusive Thresholds

If you want to include the boundary value itself (for example, identifying failing exam scores of 49.9 and below, including exactly 50):

=COUNTIF(C2:C150, "<=50")

The <= operator ensures that any cell holding exactly 50 is counted alongside lower scores.

Referencing a Dynamic Threshold Cell

Hardcoding numbers like "<20" directly into formulas requires manual editing if threshold targets change. You can link the criterion dynamically to an external cell using the ampersand (&) concatenation operator:

1. Place your threshold target value in cell D1 (e.g. enter 25).
2. In your formula cell, concatenate the less-than operator with the cell reference:

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

3. Press Enter.

Whenever you update the number in cell D1, Google Sheets recalculates the count automatically without requiring formula modifications.

Troubleshooting Common Zero Count Errors

If your COUNTIF formula unexpectedly returns 0 despite visible numbers being present:

• Numbers Stored as Text: Data imported from CSV files often formats digits as plain strings. Use VALUE or apply Format > Number > Number to convert text into true numeric values.
• Quotation Mark Syntax: Ensure the operator is inside quotes and the cell reference is outside: "<" & D1, not "<D1". Placing the cell reference inside the quotation marks causes Sheets to search literally for the text characters “D1”.