COUNTIF Greater Than in Excel: Formula and Examples

Formulas & functions · Updated November 22, 2026

To count values greater than a number in Excel, use =COUNTIF(B2:B20,">100"), assuming the values are in B2:B20 and the threshold is 100. Replace 100 with your limit; use ">=" when values equal to the limit should also count.

How the greater-than COUNTIF formula works

COUNTIF checks a range against one condition and returns the number of cells that meet it. Its arguments are the range to inspect and the criterion to apply. For a greater-than test, the criterion is made from the greater-than operator and the comparison value. Because Excel reads the criterion as text, the operator and number go together inside quotation marks.

For example, with scores in B2:B20, =COUNTIF(B2:B20,">100") counts scores strictly above 100. A value of exactly 100 does not qualify, while 100.01 does. The function returns a count, not the matching values themselves, and it does not change or sort the source data.

Suppose B2:B10 contains 45, 120, 99.5, 180, 100, 230, a blank, 80, and 150. With those values in B2:B10, =COUNTIF(B2:B10,">100") returns 4: the qualifying entries are 120, 180, 230, and 150. The result is easy to verify by checking each value against the strict threshold.

Choosing the right comparison operator

The symbol determines whether the boundary value is included. Use the operator that matches the question you are asking, rather than changing the range or adjusting the threshold to work around it.

  • > means greater than, excluding the threshold itself.
  • >= means greater than or equal to, including the threshold.
  • < means less than.
  • <= means less than or equal to.
  • <> means not equal to.

For example, assuming test scores are in B2:B10 and you want to count scores of at least 100, use =COUNTIF(B2:B10,">=100"). If the data listed above is in that range, the answer is 5 because 100 joins the four values above 100. This is a common point of confusion: greater than 100 and at least 100 are different tests.

Using a cell as the threshold

A threshold stored in a worksheet cell is easier to change than a number typed into the formula. Join the operator in quotation marks to the cell reference with an ampersand. For a threshold in D1 and values in B2:B20, use =COUNTIF(B2:B20,">"&D1). If D1 contains 100, the formula counts values above 100; change D1 to update the count without editing the formula.

For a concrete check, assume B2:B10 contains the nine values from the earlier example and D1 contains 100. With that layout, =COUNTIF(B2:B10,">"&D1) returns 4. If D1 changes to 150, it returns 2, because only 180 and 230 are strictly above 150. The cell should contain a numeric threshold, not text that merely looks like a number.

If you plan to copy the formula down a column, lock the threshold cell with dollar signs. Assuming values are in B2:B20 and the limit is in D1, use =COUNTIF(B2:B20,">"&$D$1). The absolute reference keeps D1 fixed when the formula is copied. The range B2:B20 in this example is already fixed as a range in the formula, so copying it down does not shift it.

Counting dates after a specified date

Excel stores dates as serial numbers, so COUNTIF can compare dates with the same greater-than logic used for ordinary numbers. The key is to give the criterion a genuine Excel date value rather than relying on a date string whose interpretation could vary by regional settings.

Assume dates are in C2:C6, with entries of January 1, January 2, January 31, and February 1, 2026, followed by a blank. To count dates later than January 31, 2026, use =COUNTIF(C2:C6,">"&DATE(2026,1,31)). This returns 1: February 1 is later, while January 31 is not. DATE constructs the date from year, month, and day, avoiding ambiguity between formats such as month/day/year and day/month/year.

To include January 31 as well, change the operator to greater-than-or-equal-to. If a date limit is already entered in E1, reference E1 instead of building the date in the formula. Make sure the source entries are actual Excel dates: text that looks like a date may not compare as expected. Applying a date number format can help you inspect how genuine date values display, but formatting alone does not convert text into dates.

Using COUNTIFS for additional conditions

COUNTIF handles one condition. When the question combines a greater-than test with another requirement, use COUNTIFS, which applies multiple criteria to corresponding ranges. For instance, to count sales above 100 for one region, set up one criteria range for regions and another for sales amounts.

Assume region names are in A2:A8 and sales amounts are in B2:B8. The rows contain East/120, West/200, East/90, East/150, West/110, East/80, and West/300. To count East sales greater than 100, use =COUNTIFS(A2:A8,"East",B2:B8,">100"). It returns 2, for the East sales of 120 and 150. All criteria ranges must cover corresponding rows and have the same dimensions.

If the sales threshold is kept in D1 instead, use =COUNTIFS(A2:A8,"East",B2:B8,">"&D1), assuming the regions remain in A2:A8, sales remain in B2:B8, and D1 contains 100. This also returns 2. COUNTIFS is a natural alternative when the additional conditions should all be true; it is not needed just to add a second greater-than threshold.

Common problems and how to fix them

  • Missing quotation marks: The comparison operator belongs in the criterion text. Use ">100", not an unquoted greater-than expression as the second argument.
  • Using a cell reference without joining it: A criterion such as ">D1" looks for text involving D1 rather than using the cell’s value. Join the operator and reference with &, as in ">"&D1.
  • Counting the boundary by mistake: >100 excludes 100. Choose >=100 if the boundary should count.
  • Numbers stored as text: A cell can display digits but contain text. Check how the data was imported and whether the values behave as numbers in arithmetic. Convert text-form numbers to numeric values if needed, then recalculate.
  • Unexpected blanks or mixed data: Review the selected range for headers, text entries, errors, and cells outside the intended records. COUNTIF counts cells that satisfy its criterion, so a range that includes a heading or an unrelated section can make the answer misleading.
  • Wrong argument separator: The examples use commas, as required by the usual English-language Excel syntax. Some regional settings use semicolons between arguments. If Excel rejects a formula with commas, check the list-separator setting for your installation rather than changing the criterion logic.

COUNTIF comparisons are not case-sensitive, which usually does not matter for numeric thresholds. Another distinction is that COUNTIF with a single fixed threshold compares each cell to that same value. It does not compare the value in one row with a different value in another row. For row-by-row comparisons, a different approach is needed.

For example, to count rows where the value in column B is greater than the corresponding value in column C, assume both sets of values are in rows 2 through 10. Use =SUMPRODUCT(--(B2:B10>C2:C10)). If B contains 12, 8, 15 and C contains 10, 9, 15 in the first three rows, then the full ranges return a count based on all nine row comparisons; for just those three rows, the equivalent formula using B2:B4 and C2:C4 returns 1. The first value is greater, while the second is lower and the third is equal.

When to use a formula instead of a filter

COUNTIF is useful when you need a result that updates as worksheet values change, such as a dashboard total or a quality-control count. A filter is more useful when your next step is to inspect, edit, or copy the qualifying records. Filtering does not replace the formula: it displays rows matching a condition, while COUNTIF calculates how many cells meet one condition.

For a quick visual review in Excel for Microsoft 365 on Windows, select the data and use Home > Sort & Filter > Filter, then use the column dropdown to apply a number filter. The precise filter choices depend on the selected column. On Mac or Excel for the web, the ribbon layout can differ slightly, but the COUNTIF formula and its criteria construction work the same way. Use COUNTIF when you want a persistent count in a cell; use a filter when seeing the underlying records matters more than the single total.

Frequently Asked Questions

How do I count values greater than a cell value?
Assuming the numbers are in B2:B20 and the threshold is in D1, use =COUNTIF(B2:B20,">"&D1). It compares every cell in the range with the current value in D1.

How do I count values greater than or equal to 100?
Assuming the values are in B2:B20, use =COUNTIF(B2:B20,">=100"). The equals sign includes values that are exactly 100.

Can COUNTIF count dates after today?
Assuming dates are in C2:C100, use =COUNTIF(C2:C100,">"&TODAY()). TODAY returns the current date, so the count updates when Excel recalculates. This counts dates strictly after today, not dates equal to today.

Why does COUNTIF return zero when numbers are visible?
The cells may contain text-form numbers, or the formula may point to the wrong range or threshold. Check the actual cell values, ensure the criterion uses quotation marks around the operator and number, and verify that a cell-based threshold is joined with an ampersand.

Can COUNTIF test more than one condition?
COUNTIF tests one condition. For multiple simultaneous conditions, such as a region plus a value threshold, use COUNTIFS with a range and criterion for each condition. Keep the criteria ranges aligned and the same size.

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.