COUNTIF Greater Than Cell Value: Excel Formula

Formulas & functions · Updated November 22, 2026

Use =COUNTIF(range,">"&A1) to count cells in a range that are greater than the value in cell A1. The ampersand (&) joins the comparison operator to the cell reference, which is required because COUNTIF’s criteria must be a string or a number, not a raw cell reference.

Why the Ampersand Is Necessary

In Excel, the COUNTIF function’s second argument (criteria) is evaluated as text. If you write =COUNTIF(A2:A10,">A1"), Excel reads the literal text “>A1” and looks for cells whose values are greater than the text string “A1″—which is meaningless and returns 0. The ampersand tells Excel to concatenate the greater-than symbol with the actual value stored in A1. For example, if A1 contains 50, then ">"&A1 becomes the text “>50”, which COUNTIF interprets correctly.

Basic Formula Structure and Examples

Assume you have sales numbers in B2:B20 and a threshold value in D1. To count how many sales are greater than D1, use:

=COUNTIF(B2:B20,">"&D1)

If D1 = 100 and B2:B20 contains 150, 80, 120, 90, 110, the formula returns 3 (150, 120, 110). The same logic applies to other operators:

  • Greater than or equal: =COUNTIF(B2:B20,">="&D1)
  • Less than: =COUNTIF(B2:B20,"<"&D1)
  • Less than or equal: =COUNTIF(B2:B20,"<="&D1)
  • Not equal: =COUNTIF(B2:B20,"<>"&D1)

For text criteria, you can use wildcards, but for numeric comparisons the ampersand method is the standard approach.

Using a Cell Reference for Both Range and Threshold

Sometimes you need to compare each cell in a range to a corresponding cell in another column. COUNTIF alone cannot do this row-by-row; you need SUMPRODUCT or an array formula. For example, to count rows where column A is greater than column B (A2:A10 > B2:B10), use:

=SUMPRODUCT(--(A2:A10>B2:B10))

The double unary (--) converts TRUE/FALSE to 1/0, and SUMPRODUCT sums them. This returns the count of rows where A exceeds B. This works in all Excel versions. If you have Excel 365, you can also use =SUM((A2:A10>B2:B10)*1) entered normally, but SUMPRODUCT is more universally compatible.

Common Mistakes and How to Fix Them

A frequent error is forgetting the ampersand, as described above. Another is using quotes around the cell reference: =COUNTIF(A2:A10,">"A1) is invalid syntax. Also, if your threshold cell contains text that looks like a number, COUNTIF may treat it as text and return 0. Ensure the cell is formatted as a number. If you're comparing dates, the same formula works: =COUNTIF(C2:C20,">"&E1) where E1 is a date. For blank cells, COUNTIF ignores them; to count non-blank cells greater than a value, use =COUNTIF(A2:A10,">"&0) only if you want positive numbers, but this also counts text.

Alternatives: COUNTIFS and Dynamic Arrays

If you have multiple conditions, use COUNTIFS with the same ampersand technique. For instance, to count values greater than D1 but less than D2:

=COUNTIFS(B2:B20,">"&D1,B2:B20,"<"&D2)

In Excel 365, you can use the FILTER function to return the actual values, then count them with ROWS:

=ROWS(FILTER(B2:B20,B2:B20>D1))

This returns the count of values greater than D1. FILTER requires Microsoft 365 or Excel 2021. For older versions, SUMPRODUCT is the best alternative for row-by-row comparisons.

Working with Dynamic Thresholds in Tables

If your data is in an Excel Table (Insert > Table), you can use structured references. For a table named Table1 with a column named Sales, and a threshold in cell D1, the formula is:

=COUNTIF(Table1[Sales],">"&D1)

This automatically adjusts as you add rows to the table. The same ampersand rule applies. If you want to compare each row to a value in another column within the same table, you still need SUMPRODUCT, as COUNTIF cannot do per-row comparisons.

Frequently Asked Questions

Can I use COUNTIF with a cell reference without the ampersand?

No. COUNTIF's criteria argument is text, so a raw cell reference like =COUNTIF(A1:A10,A1) works only if you're counting cells equal to the value in A1, not greater than. For comparisons, you must concatenate the operator with the cell reference using &.

How do I count cells greater than a value in another sheet?

Reference the cell normally with the sheet name: =COUNTIF(Sheet2!A1:A10,">"&Sheet2!B1). The ampersand works the same way; just include the sheet qualifier in both the range and the cell reference.

Why does my COUNTIF return 0 even though there are larger numbers?

Check that the threshold cell contains a numeric value, not text. Also, ensure the range doesn't include errors or empty cells that might distort the comparison. If the range includes text, COUNTIF ignores it, but if the threshold is text, all comparisons fail.

Can I use COUNTIF with a formula that returns a value as the threshold?

Yes, you can use a formula inside the ampersand: =COUNTIF(A1:A10,">"&MAX(B1:B5)). This counts cells greater than the maximum of B1:B5. The formula result is converted to text automatically.

What is the difference between COUNTIF and SUMPRODUCT for this?

COUNTIF is simpler and faster for a single range with a single threshold. SUMPRODUCT is needed when you want to compare two ranges row-by-row, or when you need to evaluate multiple conditions without using COUNTIFS. For simple cases, COUNTIF is preferred.

Does this work in Google Sheets?

Yes, the same formula syntax works in Google Sheets: =COUNTIF(A1:A10,">"&B1). The ampersand method is identical. However, Google Sheets also supports array formulas with ARRAYFORMULA, but COUNTIF works the same way.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.