How to Count Cells Greater Than a Number in Google Sheets
Use COUNTIF with a comparison in quotes: =COUNTIF(A2:A100,">100") counts every cell in the range larger than 100.
=COUNTIF(A2:A100,">100")
The operator and number go together inside one set of quotes. Use >=, <, <= or <> the same way.
How it works
COUNTIF counts cells that meet a single condition. For a numeric comparison, put the operator and the value in quotes as one text string, like ">100". To compare against a cell instead of a fixed number, join the operator to the cell with &.
Variations
Greater than or equal to
=COUNTIF(A2:A100,">=100")
Includes 100 itself; drop the = sign to exclude it.
Compare to a cell value
=COUNTIF(A2:A100,">"&D1)
& joins the > operator to whatever number D1 holds, so you can change the threshold without editing the formula.
Between two numbers
=COUNTIFS(A2:A100,">"&D1,A2:A100,"<"&D2)
COUNTIFS applies both limits at once; D1 is the low bound, D2 the high bound.
Examples
| Scenario | Formula |
|---|---|
| Orders above $100 | =COUNTIF(A2:A100,">100") |
| Scores at or above a passing mark in D1 | =COUNTIF(A2:A100,">="&D1) |
FAQ
Why does =COUNTIF(A:A,>100) give an error?
The comparison must be a text string. Wrap it in quotes: ">100".
How do I count greater than a cell value?
Join the operator to the cell with &: =COUNTIF(A2:A100,">"&D1).
How do I count between two numbers?
Use COUNTIFS with a lower and an upper bound: =COUNTIFS(A:A,">"&D1,A:A,"<"&D2).
Related formulas
- IF vs IFS in Google Sheets — When to Use Each
- INDEX MATCH vs VLOOKUP in Google Sheets — Which Is Better
- SUMIF vs SUMIFS in Google Sheets — When to Use Each
- TEXTJOIN vs CONCATENATE in Google Sheets — Which to Use
- UNIQUE vs Remove Duplicates in Google Sheets — Which to Use
- VLOOKUP vs XLOOKUP in Google Sheets — Which One to Use
- DATEDIF in Google Sheets
- FILTER Function in Google Sheets