Yes. To count values greater than a number with Excel COUNTIF, use a criterion such as ">100". If the values are in A2:A100 and the threshold is in E1, enter =COUNTIF(A2:A100,">"&E1). The result is the number of cells in that range whose values exceed the threshold in E1.
How COUNTIF Works
COUNTIF checks a range against one condition and returns how many cells meet it. Its structure is =COUNTIF(range,criteria): the first argument identifies the cells to inspect, and the second describes what should count. For a greater-than test, the criterion needs the greater-than symbol followed by the comparison value.
A criterion such as ">100" is written in quotation marks because it is text that tells Excel how to compare values. When the threshold is in a cell, join the operator to that cell reference with the ampersand character. The reference itself is not placed inside quotation marks. In the opening example, Excel builds the criterion from the text ">" and the value in E1.
COUNTIF tests each cell individually and adds one to its count when the condition is met. A value equal to the threshold does not count: “greater than” means strictly greater than. If you want to include values equal to the threshold, use the greater-than-or-equal-to operator instead.
Count Values Above a Fixed Number
A fixed number is useful when the cutoff will stay the same, such as counting exam scores above 80 or orders over $500. With scores in B2:B31, this formula counts scores greater than 80:
Assuming the scores are in B2:B31, enter =COUNTIF(B2:B31,">80"). If the scores are 65, 80, 81, 92, and 100, the result is 3: 81, 92, and 100 qualify, while 80 does not.
Keep the comparison operator and the fixed number together inside the criterion’s quotation marks. These are different criteria: ">80" means greater than 80, while ">=80" means greater than or equal to 80. The formula does not need a separate argument for the operator.
A fixed cutoff is straightforward, but it can be inconvenient if you need to change it regularly. A threshold stored in a worksheet cell makes the rule easier to update and allows the same threshold to be used in a visible label, chart, or other calculation.
Use a Cell as the Threshold
When the cutoff can change, put it in a cell and combine that cell reference with the operator. For example, if sales amounts are in C2:C200 and the threshold is in F2, enter =COUNTIF(C2:C200,">"&F2). Changing F2 updates the count without editing the formula.
The ampersand joins text and a value into one criterion. This matters because writing ">F2" would ask COUNTIF to compare against the literal text F2, not the value stored in that cell. Similarly, typing >F2 without the required quotes and concatenation does not form a valid COUNTIF criterion.
If you plan to copy the formula to other cells, consider whether the threshold reference should move. For a count repeated down a column while the cutoff remains in F2, make the reference absolute: =COUNTIF(C2:C200,">"&$F$2). The dollar signs keep the threshold fixed when copying. If the data range should also remain fixed, write it as $C$2:$C$200.
Greater Than or Equal To, and Other Comparisons
Choose the operator that matches the question you are asking. Greater than excludes a value exactly at the cutoff; greater than or equal to includes it. Less-than comparisons work the same way in the opposite direction. A not-equal criterion counts values that differ from the specified value.
For example, with test results in A2:A20 and the passing cutoff in D1, use =COUNTIF(A2:A20,">="&D1) to include scores exactly at the cutoff. If D1 contains 70 and the scores are 68, 70, 72, and 85, the result is 3. With a strict greater-than test, the result would be 2.
COUNTIF supports one condition at a time. If you need to count values above one limit but below another, use COUNTIFS, which applies multiple conditions to corresponding ranges. With measurements in A2:A100, a lower limit in D1, and an upper limit in E1, enter =COUNTIFS(A2:A100,">"&D1,A2:A100,"<"&E1) to count values strictly between the two limits. A value equal to either boundary is excluded.
Counting Dates Later Than a Cutoff
Excel stores genuine dates as serial numbers, so COUNTIF can compare them using the same greater-than logic. The important requirement is that the dates in the range and the cutoff are recognized as dates by Excel, rather than merely text that looks like a date.
Assuming the dates are in A2:A100 and the cutoff is in D1, enter =COUNTIF(A2:A100,">"&D1) to count dates after the cutoff. If D1 is January 1, 2025, this counts later dates but does not count January 1 itself. To include the cutoff date, use the greater-than-or-equal-to operator.
You can also create a date cutoff inside a formula. Assuming the event dates are in A2:A100, enter =COUNTIF(A2:A100,">"&DATE(2025,1,1)) to count dates after January 1, 2025. DATE uses year, month, and day arguments, which avoids ambiguity between regional date formats such as 1/2/2025.
If the result seems wrong, check whether the cells are real Excel dates. A date imported as text may not compare as expected. Changing the cell’s number format alone does not convert text into a real date; conversion may require re-importing the data, using Excel’s conversion tools, or correcting the source values.
What Counts as a Number?
COUNTIF is most dependable when the range contains consistent, genuine numeric values. Imported data can contain numbers stored as text, leading or trailing spaces, currency symbols included as text, or labels mixed into the same column. These entries may not behave like ordinary numbers in comparisons, so a result that differs from expectations often points to a data-type or data-cleaning issue.
Formatting and underlying value are different things. Applying a currency format changes how a numeric value appears, not the value itself; a cell displaying $125 can still contain the number 125. By contrast, a text entry such as “$125” may remain text and fail a numeric comparison. Check a few suspect cells by selecting them and inspecting the formula bar, and look for warning indicators that Excel may show for numbers stored as text.
Blank cells are not values above a positive cutoff, but mixed content can complicate comparisons. If a column includes text labels and numbers, do not assume every entry will be treated as a numeric measurement. Clean or separate the data when possible, and test the formula against a small set of known examples. This is especially important for imported reports and manually maintained lists.
Common COUNTIF Mistakes and Fixes
- Leaving out the quotation marks around the criterion. The operator and a typed-in cutoff belong together as text, as in
">80". For a cell threshold, quote the operator and concatenate the cell reference. - Using the wrong comparison sign.
>excludes the cutoff, while>=includes it. Confirm the boundary rule before relying on the count. - Putting the cell reference inside the criterion text.
">F2"does not use F2’s contents. Join the operator to the reference with&instead. - Including a header in the range. A header is not a measurement and can make results confusing. Use a data-only range such as A2:A100 when row 1 contains a heading.
- Using mismatched or shifting ranges. When copying formulas, check whether relative references have moved. Add dollar signs to references that must stay fixed.
- Expecting displayed rounding to control the comparison. A cell might display 80 because it is formatted with no decimal places while its stored value is 80.4. That value is greater than 80. Compare the stored value or round the underlying data if the business rule calls for rounded values.
- Using a comma when the local Excel setup expects another list separator. The formulas here use commas, as in standard English-language Excel syntax. Depending on regional settings, Excel may display or require semicolons between arguments; follow the separator used by your installation.
COUNTIF Versus COUNTIFS and Other Options
Use COUNTIF when the question has one condition, such as “How many invoices exceed $500?” Use COUNTIFS when several conditions must be true together, such as “How many invoices exceed $500 and are marked Open?” COUNTIFS is available in current Excel versions and lets you pair each criteria range with a criterion. The ranges used in one COUNTIFS formula should have matching dimensions.
For a one-off inspection, sorting or filtering the table may make the qualifying records easier to review, but a filter is not itself a reusable count formula. COUNTIF is useful when a summary cell should update as the data or threshold changes. A PivotTable can be more suitable for summarizing a large dataset by several categories, while COUNTIF remains simple for a single range and condition.
If you need to count only numeric cells above a threshold in a mixed-data range, first consider cleaning the column so values have consistent types. That is usually easier to audit than building a complex formula around inconsistent entries. After cleaning, check the result against a small manual count or a filtered view, particularly when the count will inform a report.
Frequently Asked Questions
How do I count numbers greater than a value in another cell?
Join the greater-than operator to the threshold cell. If values are in A2:A100 and the cutoff is in E1, use =COUNTIF(A2:A100,">"&E1). The formula updates when E1 changes.
How do I count values greater than or equal to a number?
Use the two-character operator >=. For example, with values in B2:B50 and a cutoff of 10, enter =COUNTIF(B2:B50,">=10"). This includes cells equal to 10.
Can COUNTIF count dates after a date?
Yes. Dates stored as Excel dates can be compared like numbers. If dates are in A2:A100 and the cutoff is in D1, use =COUNTIF(A2:A100,">"&D1). Check that imported dates are not text.
Why is my COUNTIF greater-than result zero?
Check the range, the operator, and the criterion construction. Confirm that the threshold is numeric and that the cells being counted contain real numbers rather than numbers stored as text. Also check for a header, unexpected spaces, or a cutoff that is higher than every value.
Can COUNTIF check more than one condition?
COUNTIF applies one condition. For multiple conditions that must all be satisfied, use COUNTIFS, pairing each condition with its corresponding range. For example, it can count values above a cutoff only among rows that also meet a status requirement.
Related
- Copy and Paste X Times in Excel: Repeat Cells or Rows
- Convert H:M:S to a Number in Excel
- Contact Number List Template for Excel: Setup Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Unique Excel Formula With Multiple Criteria: How It WorksTo return distinct values that meet multiple conditions in Excel for Microsoft 365, combine UNIQUE with FILTER. With…
- Calculate Dates in Excel From Today With Simple FormulasTo calculate a date a set number of days from today, enter =TODAY()+30 in any blank cell, such…
- Excel Column Name to Number: Formula and ExamplesIf cell A1 contains an Excel column name, enter =COLUMN(INDIRECT(A1&"1")) to return its number: A returns 1, Z…
- Excel MID Function: Extract Text from the Middle of a CellExcel’s MID function extracts a chosen number of characters from the middle of a text string. Its syntax…
- Excel Time Card Formula: Track Hours and OvertimeFor a basic Excel time card, enter a start time in B2, an end time in C2, and…
- Freeze a Formula in Excel: Keep Cell References FixedTo freeze a cell reference while copying a formula in Excel, edit the formula, select the reference, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.