Excel COUNTIF Over a Number: Greater Than Criteria

Formulas & functions · Updated November 19, 2026

To count numbers greater than a value in Excel, use =COUNTIF(A2:A100,">50"), replacing the range and 50 with your own data and threshold. If the threshold is in a cell, join the greater-than operator to that cell, as in =COUNTIF(A2:A100,">"&D2).

What COUNTIF means by “over a number”

In this question, “over” usually means strictly greater than: the value must exceed the threshold, not merely equal it. COUNTIF checks each cell in a range against one condition and returns the number of cells that meet it. It counts qualifying cells; it does not add their values.

For example, if the range contains 12, 50, 51, and 80, a test for values greater than 50 returns 2. The value 50 is excluded because it is equal to the threshold. That distinction matters when counting scores above a pass mark, orders larger than a target, or dates after a cutoff.

In Microsoft Excel for Microsoft 365 on Windows, COUNTIF has two arguments: the range to check and the criterion to apply. A numeric comparison criterion is written as text, including its comparison operator. In a formula, the operator and number therefore appear together inside quotation marks.

Basic formula and comparison operators

With numbers in cells A2:A10, this formula counts values strictly greater than 50:

=COUNTIF(A2:A10,">50")

The comparison operator is part of the criterion. Use these common operators to change what “over” means:

  • > counts values greater than the threshold, but not equal to it.
  • >= counts values greater than or equal to the threshold.
  • < counts values less than the threshold.
  • <= counts values less than or equal to the threshold.
  • = counts values equal to the threshold.
  • <> counts values not equal to the threshold.

For instance, if the requirement is “50 or higher,” use =COUNTIF(A2:A10,">=50"). Do not use a greater-than sign alone if equality should count. This small change is one of the most common sources of a one-cell difference in a result.

Use a cell as the threshold

A threshold stored in a worksheet cell is easier to change than a number typed into the formula. With values in A2:A100 and the threshold in D2, use:

=COUNTIF(A2:A100,">"&D2)

The ampersand joins the text operator ">" to the value in D2, creating a criterion such as ">50". Do not put the whole expression in quotation marks: doing so would make Excel treat the cell reference as literal text rather than use its value.

For an inclusive threshold with the same layout, use:

=COUNTIF(A2:A100,">="&D2)

Cell-based criteria are useful for a small report where someone may adjust a target frequently. They also make the threshold visible and editable without opening the formula. If you copy the formula down a column, consider locking the data range while leaving the threshold reference adjustable or fixed as needed. For example, dollar signs in $A$2:$A$100 keep the range in place when copied.

Worked example with a small data range

Suppose cells B2:B8 contain these values: 42, 58, 50, 73, 91, 50, and 64. With those numbers in B2:B8, this formula counts values over 60:

=COUNTIF(B2:B8,">60")

The result is 3: 73, 91, and 64 qualify. The values 42, 58, 50, 50 do not. To include 60 itself, change the criterion to greater than or equal to 60; if the range contained a 60, that value would then be included.

You can also use a decimal threshold. With the same range in B2:B8, this formula counts values greater than 57.5:

=COUNTIF(B2:B8,">57.5")

The result is 4: 58, 73, 91, and 64. Excel evaluates the actual stored values, not merely how many decimal places are displayed. A cell displayed as 58 might store 57.6, for example, depending on how its value was entered or calculated.

Count values over a threshold and meet another condition

COUNTIF handles one criterion. When you need a numeric threshold plus another rule—such as counting only “North” region sales above 500—use COUNTIFS. It applies multiple conditions to corresponding ranges, and every condition must be met for a row to count.

Assume region names are in A2:A20 and sales amounts are in B2:B20. To count rows where the region is North and sales exceed 500, use:

=COUNTIFS(A2:A20,"North",B2:B20,">500")

For a threshold held in D2 instead, with the same region and sales layout, use:

=COUNTIFS(A2:A20,"North",B2:B20,">"&D2)

The criteria ranges should have the same dimensions so Excel can compare corresponding rows. If you need “greater than 500 OR region is North,” that is different: COUNTIFS combines criteria as AND, not OR. You can add separate counts or design a helper column, depending on whether records meeting both conditions should be counted once or twice.

Common reasons the count looks wrong

If the result is unexpectedly zero or too small, check the data as well as the criterion. A cell that looks numeric may contain text, an error, or a formula returning text. Formatting a cell to display as a number does not necessarily convert text stored in it into a numeric value.

  • Check the operator. >50 excludes 50; >=50 includes it.
  • Check the range. Confirm it includes the first and last intended data rows and does not include a heading.
  • Check the threshold cell. If the criterion refers to D2, make sure D2 contains the expected numeric value rather than a label or an empty cell.
  • Check stored data types. Values imported from a text file may be numbers stored as text. Convert them to numeric values if the comparison is not behaving as expected.
  • Check for errors or unexpected formulas. A formula may display a number while returning text, or the cells being counted may contain errors.

To inspect one cell, with the value in A2, enter this test in another cell:

=ISNUMBER(A2)

It returns TRUE when A2 contains a numeric value and FALSE when it does not. For a larger imported range, you can use Excel’s warning options or convert the source data to numbers before counting. A helper formula using VALUE can also convert text that represents a number, but it may return an error for text that is not a valid numeric value.

Another frequent issue is entering an operator without quotation marks. For a fixed threshold, the criterion must be written as text, such as ">50". For a cell-based threshold, join the operator and reference with &. Also check whether the workbook uses a regional list separator other than a comma; Excel’s formula separator can vary with regional settings, although the examples here use commas.

COUNTIF compared with other counting approaches

Choose the function based on what you need to count. COUNTIF is the straightforward choice for a single threshold. COUNTIFS is a better fit when each counted row must also satisfy one or more conditions, such as a category, date range, or status.

COUNTA counts nonempty cells, regardless of whether their contents are above a number. COUNT counts numeric cells but does not apply a threshold. Neither substitutes for COUNTIF when the question is specifically how many values exceed a limit.

For a one-off check, filtering or sorting a table can help you inspect which records exceed the threshold, but it is less convenient than a formula when the result must update automatically. A PivotTable is useful for summarizing larger datasets by categories, though a simple COUNTIF formula is often quicker for a single total.

Practical tips for reliable formulas

Keep the threshold and the count range easy to identify. Descriptive column headings, a clearly labeled threshold cell, and consistent numeric data make the worksheet easier to audit. If you use a cell reference for the threshold, consider naming that cell or adding a label beside it so other users know what it represents.

When you copy a formula, choose references deliberately. In a formula filled down, a relative reference such as D2 changes to D3, which may be desired if each row has its own threshold. If all rows should use one threshold in D2, use an absolute reference instead. With values in A2:A100 and a fixed threshold in D2, the formula is:

=COUNTIF($A$2:$A$100,">"&$D$2)

Finally, test the result against a few visible values, especially at the boundary. If the threshold is 50, inspect entries equal to 50 and just above it. This quick check confirms both the selected comparison operator and the intended treatment of equality.

Frequently Asked Questions

How do I count cells greater than a number in Excel?
Use COUNTIF with a greater-than criterion. If the values are in A2:A100 and the threshold is 50, enter =COUNTIF(A2:A100,">50").

How do I count values greater than or equal to a number?
Use the >= operator. For values in A2:A100 and a threshold of 50, use =COUNTIF(A2:A100,">=50").

Can COUNTIF use a number in another cell?
Yes. Join the operator to the cell reference with an ampersand. With values in A2:A100 and the threshold in D2, use =COUNTIF(A2:A100,">"&D2).

Does COUNTIF add the numbers above the threshold?
No. COUNTIF returns how many cells meet the condition. To total values that meet a condition, use SUMIF; to count qualifying cells, use COUNTIF.

Why does COUNTIF return zero when the cells look like numbers?
The values may be stored as text, or the formula may point to the wrong range or threshold. Check a sample cell with ISNUMBER, confirm the operator, and convert text-formatted numeric data if necessary.

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.