# How to count cells greater than a number in Excel

Use COUNTIF with the operator in quotes joined to the cell: =COUNTIF(range,">"&E1).

**Excel:** `=COUNTIF(B2:B8,">"&E1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Day | Visitors |  | Target | 1500 |
| Mon | 1240 |  | Days above target |  |
| Tue | 1610 |  |  |  |
| Wed | 1585 |  |  |  |
| Thu | 1320 |  |  |  |
| Fri | 2045 |  |  |  |
| Sat | 2310 |  |  |  |
| Sun | 980 |  |  |  |

Formula in E2: `=COUNTIF(B2:B8,">"&E1)`

Result: 4

## How it works

COUNTIF needs its condition as text, so the operator goes in quotes and `&` joins it to the target: `">"&E1` becomes `">1500"`. Keeping the target in a cell means you can change it and the count updates.

## Other ways

- `=SUMPRODUCT(--(B2:B8>E1))`: The same count. SUMPRODUCT lets you write the comparison directly, without quotes.
- `=COUNTIF(B2:B8,">="&E1)`: Use >= when a day exactly on target should count. (Also counts days exactly equal to the target.)

## Common mistakes

- `=COUNTIF(B2:B8,">E1")` compares with the text "E1", not the cell, and returns 0. The cell reference has to be outside the quotes.
- Numbers stored as text are skipped. If a column is left-aligned, convert it with VALUE or Text to Columns.

## Questions

**How do I count cells between two numbers?**

Use COUNTIFS with two conditions: `=COUNTIFS(B2:B8,">="&1500,B2:B8,"<="&2000)`.

**How do I count cells less than a number?**

Swap the operator: `=COUNTIF(B2:B8,"<"&E1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/count-cells-greater-than-a-number