How to count the number of cells greater than the average in Excel

In this article, we are going to show you how to count the number of cells greater than the average in Excel.

Formula

=COUNTIFS(<numbers range>,”>“&AVERAGE(<numbers range>))

<numbers range>: The reference from the range of numbers

How it works

The formula relies on using COUNTIFS and AVERAGE formulas. The COUNTIFS function can count cells that meet the given criteria. We can use a greater than operator “>” (or any other operator as well) with the AVERAGE function a criterion. Thus, there are two steps for counting the number of cells greater than the average:

Calculate the average of numbers (AVERAGE)
Count the numbers greater than the average (COUNTIFS)

Due to need of a single criterion, you can prefer to use COUNTIF function instead of COUNTIFS as well.

A minor point to consider is that you should merge greater than operator with AVERAGE formula as a string value “>” and use "and operator" (“&”) to merge.

Here is the formula for the range B5:B12:

=COUNTIFS(B5:B12,">"&AVERAGE(B5:B12))

SUMPRODUCT Alternative

If you prefer an alternative way to count the number of cells greater than the average, you can use SUMPRODUCT function. The SUMPRODUCT function can evaluate arrays without dealing with array functions. Hence, you can use the logical statement directly in the SUMPRODUCT function.

=SUMPRODUCT(--(B5:B12>AVERAGE(B5:B12)))

=SUMPRODUCT(N(B5:B12>AVERAGE(B5:B12)))

The B5:B12>AVERAGE(B5:B12) returns an array of Boolean values which indicate if a value is greater than the average or not. To convert the logical values into numbers (1 for TRUE, 0 for FALSE) you can use unary operator “--" or N function.

Finally, the SUMPRODUCT function sums the 1 and 0 values to find the count the number of cells greater than the average.