# How to sum values above the average in Excel

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

**Excel:** `=SUMIF(B2:B8,">"&AVERAGE(B2:B8))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Store | Units |  | Total above average |
| Austin | 312 |  |  |
| Boston | 458 |  |  |
| Chicago | 389 |  |  |
| Denver | 275 |  |  |
| Miami | 501 |  |  |
| Portland | 344 |  |  |
| Seattle | 420 |  |  |

Formula in E1: `=SUMIF(B2:B8,">"&AVERAGE(B2:B8))`

Result: 1768

## 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.

## Other ways

- `=SUMPRODUCT((B2:B8>AVERAGE(B2:B8))*B2:B8)`: The 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-values-above-average