Spreadsheet Practice

How to count cells greater than a number in Excel

Formula (Excel and Google Sheets)

=COUNTIF(B2:B8,">"&E1)

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

Result on the example
4 in E2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

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.

=COUNTIF(B2:B8,">"&E1)

Result4

  1. B2:B87 rows × 1 col: 1,240, 1,610, 1,585, 1,320, …

    A range: 7 cells in one column.

  2. E11,500

    The value in E1.

  3. ">"&E1">1500"

    The & operator joins as text.

  4. COUNTIF(B2:B8,">"&E1)4

    COUNTIF: Counts cells that meet one condition.

    • rangeB2:B87 rows × 1 col: 1,240, 1,610, 1,585, 1,320, …
    • criteria">"&E1">1500"

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(B2:B8,">"&E1). We ran it in Google Sheets mode on the same data and it returns 4.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--(B2:B8>E1))4The same count. SUMPRODUCT lets you write the comparison directly, without quotes.
=COUNTIF(B2:B8,">="&E1)4Different resultUse >= 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count cells greater than a number

In E2, count the days with more visitors than the target in E1.

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).

Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.