Why does COUNTIF return 0 when the criteria refers to a cell?

Q: How do I count values equal to a cell?

Give the cell on its own: =COUNTIF(B2:B8,E1).

Formula (Excel and Google Sheets)

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

The cell reference is inside the quotes. Join the operator to the cell with &: =COUNTIF(B2:B8,">="&E1), not ">=E1".

Result on the example: 5 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's condition is text. In ">=E1" the E1 sits inside the quotes, so COUNTIF looks for values of at least the text "E1" and finds none. Joining the operator to the cell with &, ">="&E1, builds the text ">=500" from the cell's value.

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

Result5

B2:B87 rows × 1 col: $820, $450, $1,275, $500, …
A range: 7 cells in one column.
E1$500
The value in E1.
">="&E1">=500"
The & operator joins as text.
COUNTIF(B2:B8,">="&E1)5
COUNTIF: Counts cells that meet one condition.
- rangeB2:B87 rows × 1 col: $820, $450, $1,275, $500, …
- criteria">="&E1">=500"

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

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=SUMPRODUCT(--(B2:B8>=E1))	5	Writes the comparison directly, without building a text condition.
=COUNTIF(B2:B8,">=500")	5	A typed number works inside the quotes, but has to be edited whenever the minimum changes.

Common mistakes

The same applies to SUMIF, AVERAGEIF and the IFS versions, and to "<>"&E1 for "not equal to".
For "contains", join wildcards on both sides: "*"&E1&"*".
A date in the cell works the same way: ">="&E1 with a date in E1.

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.