COUNTIF Greater Than, Less Than and Equal To

To use COUNTIF greater than a number, put the operator and the number together inside double quotes: =COUNTIF(B2:B8,">50"). The same pattern handles less than, equal to and not equal to; only the operator changes. This guide shows every comparison on one small table, then covers cell references, averages, ranges and the reasons a count comes out wrong.

How to count cells greater than a number#

COUNTIF counts the cells in a range that meet one condition, called the criteria. For "greater than", the criteria is the > sign followed by the number, all inside quotes:

=COUNTIF(range, ">number")

Every example uses these test scores in A1:B8:

To count the scores above 50:

1. Click an empty cell, such as D2.
2. Type =COUNTIF(B2:B8,">50").
3. Press Enter.

The result is 4: 72, 88, 91 and 63. Dev's 50 is not counted, because 50 is not greater than 50. The quotes are required. Without them, =COUNTIF(B2:B8,>50) is a syntax error and Excel won't accept the formula. If COUNTIF is new to you, read how to use COUNTIF first.

COUNTIF less than, equal to and not equal to#

All six comparison operators work the same way inside COUNTIF. Here they are on the score column:

A few rules:

• Equal to needs no operator. 50 and "=50" give the same result.
• Greater than or equal to is >=. The reverse order, =>, doesn't work. The same goes for <=.
• Not equal to is <>. The != used in programming isn't an Excel operator.
• Keep the criteria tight, with no spaces: ">50".

Notice that "greater than 50" plus "less than or equal to 50" covers every score: 4 + 3 = 7. That is a quick way to check your work. The lesson on TRUE and FALSE comparisons practises these operators on their own.

How to use COUNTIF greater than a cell value#

Put the limit in a cell when you want to change it without editing the formula. Type 60 in E1, then:

=COUNTIF(B2:B8,">"&E1)

The result is 4 (72, 88, 91 and 63). The & joins the text ">" to the value in E1, so Excel sees ">60". Change E1 to 80 and the count drops to 2.

The common mistake is to write ">E1". Inside quotes, E1 is just the letters E and 1, so Excel looks for scores greater than the text "E1" and returns 0. The rule is: the operator goes in quotes, the cell reference stays outside.

If you fill this formula down a column with a different limit on each row, lock the range with $ signs: =COUNTIF($B$2:$B$8,">"&E1).

How to count values greater than the average#

The criteria can include a function, joined with & in the same way. To count the scores above the average:

=COUNTIF(B2:B8,">"&AVERAGE(B2:B8))

The average of the seven scores is about 63.86. Three scores beat it: 72, 88 and 91, so the result is 3. Farid's 63 just misses. The same pattern works with MAX, MIN or MEDIAN, for example "<"&MEDIAN(B2:B8) for the scores below the middle value.

How to count between two numbers#

COUNTIF takes one condition, so for a range such as "50 to 80" use COUNTIFS with two conditions on the same column:

=COUNTIFS(B2:B8,">=50",B2:B8,"<=80")

The result is 3: 72, 50 and 63. A row is counted only when it meets both conditions. The guide to COUNTIF between two numbers shows more examples, and COUNTIF vs COUNTIFS explains when you need each function.

Greater than with dates and text#

Dates are numbers in Excel, so the same operators work. Build the date with DATE rather than typing it as text:

=COUNTIF(C2:C8,">"&DATE(2024,6,30))

This counts dates after 30 June 2024. See COUNTIF with dates for before, after and between.

With text, > and < compare alphabetically, so ">M" counts words that come after M in the alphabet. That is rarely what you want. For text, you usually need an exact match, <>, or a wildcard such as "*blue*", covered in COUNTIF contains text.

Why COUNTIF greater than returns 0 or the wrong number#

When the count looks wrong, check these causes in order:

1. Numbers stored as text. Imported numbers are sometimes text. COUNTIF with ">50" skips them. Real numbers sit on the right of the cell by default; text sits on the left. Convert them with VALUE or Data > Text to Columns.
2. A cell reference inside the quotes. Write ">"&E1, not ">E1".
3. Wrong operator order. Use ">=50", not "=>50".
4. Wrong range. Make sure the range is the number column and includes every row.
5. Blank cells. COUNTIF with ">50" ignores empty cells, but "<>50" counts them, because a blank is not equal to 50. To count only filled cells, see COUNTIF not blank.

If you need a label on each row rather than a total, use IF instead. The guide to IF greater than shows how.

COUNTIF greater than in a summary table#

To compare several limits at once, list them down a column and point one formula at each. With 50, 60, 70 and 80 in E2:E5, type this in F2 and fill down:

=COUNTIF($B$2:$B$8,">"&E2)

The results are 4, 4, 3 and 2. The $ signs keep the score range fixed while E2 changes to E3, E4 and E5. A table like this quickly shows how many candidates would pass at each possible pass mark.

Practise it#

Operators in quotes and the & join are small details that trip people up in timed tests. Practise COUNTIF in a live spreadsheet: you type the criteria and the lesson checks it instantly. The COUNTIF function guide is a quick reference for the syntax when you need it.