Spreadsheet Practice

How to sum values above the average in Excel

Formula (Excel and Google Sheets)

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

Build the condition from AVERAGE: =SUMIF(B2:B8,">"&AVERAGE(B2:B8)).

Result on the example
1768 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, about 385.6, and ">"& turns it into the condition "greater than 385.57…". SUMIF then adds the four stores above it: Boston, Chicago, Miami and Seattle.

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

Result1768

  1. B2:B87 rows × 1 col: 312, 458, 389, 275, …

    A range: 7 cells in one column.

  2. AVERAGE(B2:B8)385.5714286

    AVERAGE: Arithmetic mean of the numbers.

    • number1B2:B87 rows × 1 col: 312, 458, 389, 275, …
  3. ">"&AVERAGE(B2:B8)">385.571428571429"

    The & operator joins as text.

  4. SUMIF(B2:B8,">"&AVERAGE(B2:B8))1768

    SUMIF: Adds values where the range meets a condition.

    • rangeB2:B87 rows × 1 col: 312, 458, 389, 275, …
    • criteria">"&AVERAGE(B2:B8)">385.571428571429"

In Google Sheets

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

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT((B2:B8>AVERAGE(B2:B8))*B2:B8)1768The comparison written directly; easy to extend with other tests.

Common mistakes

  • Putting AVERAGE inside the quotes (">AVERAGE(B2:B8)") compares with the text and returns 0.
  • The average includes every store, so one huge value raises it and fewer stores count as above average. Consider the median for skewed data.

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.

Sum values above the average

In E1, add up the stores' units that are above the average of B2:B8.

Questions

How do I count the stores above average?

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

How do I sum values below the average?

Swap the operator: =SUMIF(B2:B8,"<"&AVERAGE(B2:B8)).

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.