Excel COUNTIF Greater Than: Formula & Examples

Formulas & functions · Updated November 22, 2026

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

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.