To count cells in Excel that are greater than a specific value, use the formula =COUNTIF(range,">"&value). For example, =COUNTIF(A2:A10,">5") counts how many cells in A2:A10 contain numbers greater than 5. This works for numbers, dates, and times, and the criteria can be a number, a cell reference, or a formula result.
How COUNTIF Handles Greater Than Criteria
COUNTIF takes two arguments: range (the cells to check) and criteria (the condition). For greater than, you write the comparison operator inside double quotes, followed by the value. The ampersand (&) concatenates the operator with a cell reference or another formula. If you type the number directly, you can put it inside the quotes like ">10" or use the ampersand with a cell reference like ">"&B1.
Excel treats text and blank cells as ignored by COUNTIF when the criteria is numeric. Dates are stored as serial numbers, so the same logic applies—just use a date value or a cell containing a date. For example, =COUNTIF(C2:C20,">"&DATE(2025,1,1)) counts dates after January 1, 2025.
Using Cell References and Formulas in Criteria
Instead of hardcoding a number, reference a cell to make the formula dynamic. With the threshold in B1, use =COUNTIF(A2:A10,">"&B1). This updates automatically when B1 changes. You can also embed a formula: =COUNTIF(A2:A10,">"&AVERAGE(A2:A10)) counts cells above the average of that same range. Note that the range and the average range can be different, but using the same range is common.
For greater than or equal to, change the operator to ">=". For less than, use "<". For between two values, subtract two COUNTIFs: =COUNTIF(A2:A10,">5")-COUNTIF(A2:A10,">10") counts numbers greater than 5 and less than or equal to 10.
Counting Greater Than with Multiple Conditions (COUNTIFS)
When you need to count cells that are greater than a value AND meet another condition, use COUNTIFS. For example, with sales in B2:B20 and region in A2:A20, to count sales over $500 in the "East" region: =COUNTIFS(A2:A20,"East",B2:B20,">500"). COUNTIFS accepts multiple range/criteria pairs, and all conditions must be true for a cell to be counted. Unlike COUNTIF, COUNTIFS is available in all modern Excel versions (2010 and later).
Common Errors and How to Fix Them
One frequent mistake is forgetting the quotes around the operator. Writing =COUNTIF(A2:A10,>5) causes a syntax error. Always use ">5" or ">"&B1. Another issue is using a cell reference without the ampersand: =COUNTIF(A2:A10,">B1") treats ">B1" as text, not a reference, and returns 0. Use ">"&B1.
If your data contains numbers stored as text, COUNTIF may ignore them. To fix, select the column and use the Text to Columns wizard (Data > Text to Columns > Finish) to convert text to numbers. Also, be careful with leading or trailing spaces in criteria—use =COUNTIF(A2:A10,">"&TRIM(B1)) if needed.
Alternatives: SUMPRODUCT and FILTER
For more flexibility, SUMPRODUCT can count with greater-than criteria and even handle arrays without Ctrl+Shift+Enter. Example: =SUMPRODUCT(--(A2:A10>5)) counts cells greater than 5. The double negative converts TRUE/FALSE to 1/0. This is useful when you need to count based on a calculation, like =SUMPRODUCT(--(A2:A10*2>10)).
In Excel 365, the FILTER function can return the actual cells, and you can wrap it in COUNTA: =COUNTA(FILTER(A2:A10,A2:A10>5)). This is more dynamic but requires Microsoft 365 or Excel 2021. For simple greater-than counts, COUNTIF is faster and simpler.
Frequently Asked Questions
How do I count cells greater than zero? Use =COUNTIF(A2:A10,">0"). This counts all positive numbers, ignoring blanks and text.
Can I use greater than with text? Yes, but it compares alphabetically. For example, =COUNTIF(A2:A10,">m") counts cells that start with letters after "m" (case-insensitive). This is rarely useful, but it works.
Why does my COUNTIF return 0 when I reference a cell? Check that the cell reference is not inside quotes. Correct: =COUNTIF(A2:A10,">"&B1). Incorrect: =COUNTIF(A2:A10,">B1").
How do I count dates greater than today? Use =COUNTIF(A2:A10,">"&TODAY()). This counts dates after the current date, and updates daily.
Does COUNTIF work with whole columns? Yes, you can use =COUNTIF(A:A,">5") to count the entire column. But this may slow down calculation if the column has many rows; better to limit the range.
What if I need to count greater than in a filtered list? COUNTIF ignores filters and counts all cells in the range. Use SUBTOTAL with function 2 or 3 (COUNTA) combined with a helper column, or use the AGGREGATE function. For example, =SUBTOTAL(2,A2:A10) counts visible cells, but it doesn't apply the greater-than condition. You'd need a helper column with an IF formula.
Practical Example with Sales Data
Suppose you have sales amounts in B2:B20 and you want to count how many sales exceeded $1,000. Place the threshold in D1 (1000). In D2, enter =COUNTIF(B2:B20,">"&D1). The result is the count of cells in B2:B20 that are greater than 1000. If you have 5 sales above that amount, the formula returns 5. For a quick check, you can also use conditional formatting to highlight those cells: select B2:B20, go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than, and enter 1000.
Related
- MS Access Split Form: Put the Datasheet on the Bottom
- Java split() Usage: Syntax, Examples, and Common Traps
- Databricks SQL CONTAINS: Find Text in a String
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Time Difference in Minutes: Formulas and TipsTo calculate the minutes between a start time in A2 and an end time in B2, enter =(B2-A2)*1440…
- XLOOKUP Examples for Beginners: Formulas, Tips, and FixesXLOOKUP finds a value in one range and returns the corresponding value from another; for example, with product…
- ISERROR in Excel: Check for Errors in Your FormulasIn Excel, use ISERROR to check whether a value or calculation produces an error: enter a formula such…
- Weighted Average in Excel: Formula and ExamplesTo calculate a weighted average in Excel, multiply each value by its weight, add those products, then divide…
- Excel Formula for Checkbook Register: Setup GuideThe core formula for a checkbook register in Excel is a running balance formula: =SUM($D$2:D2) entered in the…
- Link Cells in Excel: Formulas, Sheets, and WorkbooksYes. In Excel, you can link cells with a formula that displays another cell’s value, whether the source…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.