Count Values Above the Average

Excel Formulas › Count

All versionsCOUNTIF

How many values beat the mean? Nest AVERAGE inside COUNTIF’s criteria to count everything above (or below) the average in one formula.

Quick formula: count values greater than the average of B2:B100:
=COUNTIF(B2:B100, ">"&AVERAGE(B2:B100))
AVERAGE computes the mean; ">"& builds the criteria; COUNTIF counts the values above it.

Functions used (tap for the full reference guide):

COUNTIFAVERAGE

The example

Of these, how many beat the average of 30?

AB
1StatValue
2Average30
3# above average2

The formula

Count beating the mean:

=COUNTIF(B2:B100, ">"&AVERAGE(B2:B100)) // how many above the mean

How it works

Build the criteria from the average:

  1. AVERAGE(B2:B100) computes the mean.
  2. Concatenate it into a criteria string: ">"&AVERAGE(…).
  3. COUNTIF counts every value matching that condition — the above-average count.
  4. Swap > for < to count below average; >= to include those exactly at the mean.

Above average within a group? Combine with COUNTIFS and an AVERAGEIF: count rows in a region that beat that region’s average. The pattern — nest the stat into the criteria — works for MEDIAN, percentiles, and more.

Try it: interactive demo

Live demo

Values; count those above the mean.

Avg · Above

Variations

Below average

Flip the operator:

=COUNTIF(rng, "<"&AVERAGE(rng))

% above average

Share:

=COUNTIF(rng, ">"&AVERAGE(rng))/COUNT(rng)

Above the median

Use MEDIAN:

=COUNTIF(rng, ">"&MEDIAN(rng))

Pitfalls & errors

Concatenate, don’t embed. Use ">"&AVERAGE(…) — putting AVERAGE inside the quotes makes it literal text.

Ties at the mean. > excludes values exactly equal to the average; use >= to include them.

Blanks/text. AVERAGE and COUNT ignore text and blanks, but a stray 0 shifts the mean.

Practice workbook

📊
Download the free Count Values Above the Average practice workbook
An above-average counter with the below, percent, and median variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I count values above the average in Excel?
Nest AVERAGE in the criteria: =COUNTIF(range, ">"&AVERAGE(range)). It counts everything above the mean.
How do I count below average?
Use the < operator: =COUNTIF(range, "<"&AVERAGE(range)).
Why does my COUNTIF return 0?
You probably put AVERAGE inside the quotes. Concatenate it: ">"&AVERAGE(range), not ">AVERAGE(range)".

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Highlight above average · Count with criteria · Median by group

Function references: COUNTIF · AVERAGE