Count Cells Above Average in Excel With COUNTIF

To count cells above average in Excel, put AVERAGE inside COUNTIF's criteria: =COUNTIF(C2:C9,">"&AVERAGE(C2:C9)). AVERAGE works out the mean, and COUNTIF counts every cell greater than it. This guide is for anyone answering "how many beat the average?" in a report or job test, and it covers below average, group averages, wildcards and the one mistake that makes the formula return 0.

The formula to count above average in Excel#

You need two functions working together. AVERAGE finds the mean of the range. COUNTIF counts the cells that meet a condition. The trick is building the condition:

=COUNTIF(range,">"&AVERAGE(range))

COUNTIF's criteria is always text, such as ">4000". The & builds that text from the operator and a live number, so the criteria updates whenever the data changes. The guide to COUNTIF with criteria examples explains criteria in general.

Example: count sales reps above average#

Here is a sales table in A1:C9:

First, the average: =AVERAGE(C2:C9) returns 4,087.5 (32,700 divided by 8). Then count the reps above it:

=COUNTIF(C2:C9,">"&AVERAGE(C2:C9))

The result is 4: Ana, Chen, Fay and Hana. Excel turns the criteria into ">4087.5" behind the scenes and counts every sale bigger than that.

If you already have the average in a cell, say F1, point at the cell instead: =COUNTIF(C2:C9,">"&F1). That is easier to read and lets you show the average next to the count. The AVERAGE function guide and AVERAGE, MIN and MAX explained cover the averaging side.

How to count below average, or equal to it#

Change the operator and keep everything else the same:

Here no sale equals the average exactly, so ">" and ">=" give the same answer. With whole-number scores, such as test marks, a value can land exactly on the average, and then the choice matters. Decide whether "at average" counts as a pass before you write the formula. For more on these operators, see COUNTIF greater than and less than.

For the share, format the cell as a percentage (Ctrl+Shift+% on both Windows and Mac).

How to count above the average of a group#

Sometimes "above average" should mean above the average for that region, not the whole team. AVERAGEIF works out a conditional average, and COUNTIFS counts with two conditions:

=COUNTIFS(B2:B9,"North East",C2:C9,">"&AVERAGEIF(B2:B9,"North East",C2:C9))

• AVERAGEIF(B2:B9,"North East",C2:C9) is the North East average: (4,200 + 3,800) / 2 = 4,000.
• COUNTIFS counts rows where the region is North East and sales are above 4,000.

The result is 1 (Ana). Put the region name in a cell, such as G2, and use G2 in both places to build a summary for every region. The guide to AVERAGEIF in Excel covers conditional averages in more depth.

Use wildcards to count above average for similar names#

A wildcard is a symbol that stands for unknown characters: * means any number of characters. With it, one condition can match several regions at once. To count reps in any North region whose sales beat the team average:

=COUNTIFS(B2:B9,"North*",C2:C9,">"&AVERAGE(C2:C9))

"North*" matches North East and North West. The result is 2: Ana (4,200) and Chen (5,600). Gus is in the North West but below the average.

To compare the North reps with the North average instead, use the wildcard inside AVERAGEIF as well:

=COUNTIFS(B2:B9,"North*",C2:C9,">"&AVERAGEIF(B2:B9,"North*",C2:C9))

The North average is 4,225, so the result is 1: only Chen. Ana's 4,200 is just below it. Read more in the guide to wildcards in COUNTIF and SUMIF criteria.

Why COUNTIF above average returns 0 or a wrong count#

Most problems come from how the criteria is written. Check these:

1. AVERAGE inside the quotes. =COUNTIF(C2:C9,">AVERAGE(C2:C9)") returns 0, because inside quotes AVERAGE(C2:C9) is just text, so COUNTIF compares the numbers with a word and none of them match. Keep only the operator in quotes.
2. Different ranges. =COUNTIF(C2:C9,">"&AVERAGE(C2:C20)) compares with the average of a bigger range. Use the same range in both places, or lock both with $ before filling.
3. Zeros instead of blanks. AVERAGE ignores empty cells but counts zeros. A column full of 0s for "no data" drags the average down, so more values look above average.
4. Numbers stored as text. AVERAGE and COUNTIF both skip them, so the count is lower than you expect. Convert them with VALUE or Text to Columns.

Another way to write the same count is =SUMPRODUCT(--(C2:C9>AVERAGE(C2:C9))). It compares each cell with the average, turns TRUE into 1 with --, and adds them up. It also returns 4. COUNTIF is easier to read, so use it unless you need conditions COUNTIF can't handle.

How to highlight values above average#

To see the above-average cells rather than count them, use conditional formatting. Select C2:C9, then go to Home > Conditional Formatting > Top/Bottom Rules > Above Average and pick a colour. Excel shades Ana, Chen, Fay and Hana, the same four that COUNTIF counted. Use both together: the count for the report, the colour for the reader.

Practise it#

Try the exercise on this page: one COUNTIF counts the reps who beat the team average. Then practise COUNTIF in a live spreadsheet, where you type each formula and it checks it instantly. Build your AVERAGE skills in the AVERAGE, MIN and MAX lesson, and try wildcard conditions in the criteria and wildcards lesson. The COUNTIF function guide is a handy reference.