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

The cell reference is inside the quotes. Join the operator to the cell with &: =COUNTIF(B2:B8,">="&E1), not ">=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 |
| --- | --- | --- | --- | --- |
| Invoice | Amount |  | Minimum | 500 |
| INV-1 | 820 |  | Count |  |
| INV-2 | 450 |  |  |  |
| INV-3 | 1275 |  |  |  |
| INV-4 | 500 |  |  |  |
| INV-5 | 90 |  |  |  |
| INV-6 | 640 |  |  |  |
| INV-7 | 505 |  |  |  |

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

Result: 5

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

## Other ways

- `=SUMPRODUCT(--(B2:B8>=E1))`: Writes the comparison directly, without building a text condition.
- `=COUNTIF(B2:B8,">=500")`: 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.

## Questions

**Why doesn't ">=E1" give an error?**

It is a valid condition, just not the one you meant: a comparison with the text E1.

**How do I count values equal to a cell?**

Give the cell on its own: `=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-30.

Source: https://spreadsheetpractice.com/formulas/countif-criteria-with-cell-reference