# How to count cells above or below the average in Excel

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

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

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

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Rep | Sales |  | Above average |
| Ana | 4200 |  |  |
| Ben | 5150 |  |  |
| Cara | 3980 |  |  |
| Dev | 6100 |  |  |
| Eli | 4720 |  |  |
| Fay | 3550 |  |  |
| Gus | 5480 |  |  |
| Hana | 4950 |  |  |
| Ivo | 2900 |  |  |
| Jo | 5600 |  |  |

Formula in E1: `=COUNTIF(B2:B11,">"&AVERAGE(B2:B11))`

Result: 6

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

## Other ways

- `=SUMPRODUCT(--(B2:B11>AVERAGE(B2:B11)))`: The same count as a direct comparison.
- `=COUNTIF(B2:B11,"<"&AVERAGE(B2:B11))`: Below 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.

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

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/count-cells-above-average