Count Cells Greater Than: Excel & Sheets Guide

This guide explains how to count cells that are greater than a specific value in Microsoft Excel and Google Sheets, showing the exact formulas, how to adapt them for different data types, and how to avoid common mistakes. You’ll learn the COUNTIF function, its variations, and practical examples for numbers, dates, and text.

Formula

The core formula for counting cells greater than a value is COUNTIF. Its syntax is:

=COUNTIF(range, ">"&criteria)

Where:

• range – the group of cells you want to check (e.g., A2:A100).
• criteria – the number you want to compare against (e.g., 10).
• “>”&criteria – the text string that tells Excel to count only cells whose value is strictly greater than the criteria.

The ampersand (&) joins the comparison operator (“>”) with the actual number. You can also use ">=5" for greater than or equal to, or "<5" for less than.

Worked Example

Suppose you have the following numbers in cells A1 through A5: 5, 12, 8, 15, 3. You want to count how many are greater than 10.

1. Set your threshold value to 10.
2. In a blank cell, type: =COUNTIF(A1:A5, ">"&10)
3. Excel evaluates each cell: 5 (no), 12 (yes), 8 (no), 15 (yes), 3 (no).
4. The formula returns 2.

If you use the calculator above with the default data list (5, 12, 8, 15, 3) and threshold 10, it also returns 2.

How COUNTIF Works

COUNTIF is a statistical function that counts the number of cells within a range that meet a single condition. The condition is written as a text string, which is why the comparison operator is enclosed in quotation marks. When you use a cell reference for the criteria (e.g., =COUNTIF(B2:B50, ">"&D1)), the ampersand is essential—it tells Excel to combine the operator with the value in D1.

The function only counts cells that contain numbers. Text cells, blanks, and error values are ignored. If your range contains numeric values stored as text (often indicated by a green triangle in Excel), they will not be counted. You can convert them to real numbers using the VALUE function or Text to Columns.

Using COUNTIF with Dates and Text

The same logic applies to dates. To count dates after a certain date, use:

=COUNTIF(A:A, ">"&DATE(2025,1,1))

This counts all dates from January 1, 2025 onward. For text, the comparison is case-insensitive and based on alphabetical order. For example, =COUNTIF(A:A, ">"&"m") counts all cells that start with a letter after 'm' (n through z). This is rarely useful, but it works.

How to Choose the Right Criteria

When deciding which comparison operator to use, remember:

• ">" – strictly greater than (excludes the threshold itself).
• ">=" – greater than or equal to (includes the threshold).
• "<" – strictly less than.
• "<=" – less than or equal to.
• "=" – equal to (you can omit the operator and just use the number).

If you need to count values between two numbers, use COUNTIFS with two conditions: =COUNTIFS(A:A, ">"&10, A:A, "<"&20).

Common Mistakes and How to Avoid Them

• Forgetting the ampersand: Writing =">10" works for a literal number, but if you use a cell reference, you must write ">"&D1. Without the ampersand, Excel treats the cell reference as text and returns 0.
• Using the wrong range size: Ensure the range covers exactly the cells you want. If you include header text, it will be ignored, but if you include extra blank cells, they won't affect the count.
• Mixing data types: If some cells contain numbers and others contain text numbers, COUNTIF will only count the true numbers. Use the ISNUMBER function to check.
• Using quotes around a number: =">"&10 is correct. =">10" is also correct. But =">"&"10"" is wrong – it treats 10 as text.

Alternatives to COUNTIF

If you need more flexibility, consider these alternatives:

• COUNTIFS – counts cells meeting multiple criteria across different ranges. Essential for "greater than X and less than Y" scenarios.
• SUMPRODUCT – can count with array logic, e.g., =SUMPRODUCT(--(A1:A100>10)). This is useful when you need to evaluate a condition that COUNTIF can't handle directly, like counting cells where the length is greater than a number.
• FILTER (Excel 365/Google Sheets) – returns the actual cells, and you can wrap it in COUNTA to count them: =COUNTA(FILTER(A:A, A:A>10)).
• Pivot Tables – for large datasets, a pivot table with a value filter can count greater-than values without writing formulas.

Frequently Asked Questions

How do I count cells greater than zero?

Use =COUNTIF(range, ">0"). This counts all positive numbers in the range. It's a common way to count non-empty numeric cells, but note that it will not count text or blank cells.

Can I count cells greater than a value in another sheet?

Yes, reference the other sheet in the range. For example, =COUNTIF(Sheet2!A:A, ">"&10) counts all cells in column A of Sheet2 that are greater than 10.

Why does my COUNTIF return 0 when I know there are matching cells?

Most likely, the criteria is written as text instead of a number, or the range contains numbers stored as text. Check that the threshold cell contains a number, not a string. Also, use the ISNUMBER function on a sample cell to verify the data type.

How do I count cells greater than a date in Excel?

Use =COUNTIF(range, ">"&DATE(2025,1,1)) or reference a cell containing a date. Excel stores dates as serial numbers, so the comparison works directly. Ensure the date is a real date, not text.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF accepts a single condition. COUNTIFS accepts multiple conditions, each with its own range. For example, to count cells greater than 10 and less than 20, you must use COUNTIFS. COUNTIF can only handle one comparison.

Can I use COUNTIF with a cell reference for the threshold?

Yes, that's the recommended way. Write =COUNTIF(A:A, ">"&B1) where B1 contains the threshold. This makes it easy to change the threshold without editing the formula.

Related

• ESP32 Dual Core Scheduling: How to Use Both Cores
• Beats Solo 4 Controls: Buttons & Mic Guide
• JS Custom Actions: Functions vs Classes

in threshold | Threshold value | - | 10 | 0 | 1000000
in data_list | Data list | - | 5, 12, 8, 15, 3 | 0 | 1000000
out count_greater | Count greater than | - | sum(if(data_list > threshold, 1, 0)) | 0