Spreadsheet Practice

How to count cells above or below the average in Excel

Formula (Excel and Google Sheets)

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

Join the operator to AVERAGE of the same range: =COUNTIF(B2:B11,">"&AVERAGE(B2:B11)). Swap in "<" to count below.

Result on the example
6 in E1
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

How it works

AVERAGE works out the mean first, 4,663 here, and ">"& turns it into the criteria ">4663". COUNTIF then counts the six reps above it.

Because the average is calculated inside the formula, the count updates when any sales figure changes.

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

Result6

  1. B2:B1110 rows × 1 col: $4,200, $5,150, $3,980, $6,100, …

    A range: 10 cells in one column.

  2. AVERAGE(B2:B11)4663

    AVERAGE: Arithmetic mean of the numbers.

    • number1B2:B1110 rows × 1 col: $4,200, $5,150, $3,980, $6,100, …
  3. ">"&AVERAGE(B2:B11)">4663"

    The & operator joins as text.

  4. COUNTIF(B2:B11,">"&AVERAGE(B2:B11))6

    COUNTIF: Counts cells that meet one condition.

    • rangeB2:B1110 rows × 1 col: $4,200, $5,150, $3,980, $6,100, …
    • criteria">"&AVERAGE(B2:B11)">4663"

In Google Sheets

The same formula works in Google Sheets: =COUNTIF(B2:B11,">"&AVERAGE(B2:B11)). We ran it in Google Sheets mode on the same data and it returns 6.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--(B2:B11>AVERAGE(B2:B11)))6The same count as a direct comparison.
=COUNTIF(B2:B11,"<"&AVERAGE(B2:B11))4Different resultBelow the average. Counts the 4 reps below average.

Common mistakes

  • Writing ">AVERAGE(B2:B11)" inside the quotes compares with that text and returns 0. The function must be outside the quotes.
  • Above and below don't always add up to the total: a value exactly equal to the average is in neither count.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count cells above or below the average

In E1, count the reps whose sales are above the average of B2:B11.

Questions

How do I count values above the average of a group?

Use AVERAGEIF for the group average and COUNTIFS for the count: =COUNTIFS(C2:C11,"East",B2:B11,">"&AVERAGEIF(C2:C11,"East",B2:B11)).

How do I count values above the median instead?

Swap the function: =COUNTIF(B2:B11,">"&MEDIAN(B2:B11)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.