Formula Friday – Count How Many Numbers In A List Are Greater Than The Average Using The COUNTIF Function.

Written by Barbara in Blog,Excel Tips& Tricks,Formula Friday,Formulas

Hello Excellers and welcome back another #FormulaFriday #Excel blog post in my 2020 Excel series.  I will share a solution to a question asked recently.  How to find how many numbers in your list are greater than the average.   This is a useful formula solution using the COUNTIF Function.  It can be used to answer a number of questions such as

HOW MANY?

  • students scored higher than average
  • employees are paid higher than the average
  • sales reps made sales higher than the average
The list really can go on.  Once you get an understanding of the syntax sky is the limit with this formula.
Excel Macro
First things first let’s take a look at understanding the COUNTIF function.

The COUNTIF Syntax.

COUNTIF is one of Excel’s statistical functions, to count the number of cells that meet a criterion you specify.

=COUNTIF(range, criteria)

Where

range  – is the range of cells that you want to count based on the criteria

criteria – is the criteria used to determine which cells to count

An Example Using COUNTIF Function.

Let’s take an example and find out how many employees are paid higher than the average salary.

First, let’s calculate the average salary.  Easy.  See below.

FORMULA FRIDY USE COUNTIF TO COUNT EMPLOYEES OVER AVERAGE SALARY

We have used a value in another cell as part of the criteria, so we have used the ampersand (&) character to concatenate the cell value and the greater than symbol.  As we are using a text string ( the greater than symbol) we must use the double quotation marks with it.

Now we have the average salary we can calculate using the COUNTIF Function how many employees earn more than the average.

FORMULA FRIDY USE COUNTIF TO COUNT EMPLOYEES OVER AVERAGE SALARY2
Excel has correctly identified that there are 2 employees that have salaries higher than the average.  Easy huh?.  All we need to do is use the COUNTIF Function.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Formula Friday Series Click The Link Below

 

 

Learn Excel Dashboard Course

 

 

Barbara

Barbara is an experienced data analyst with over 20 years of experience. Author of the How To Excel At Excel Top 50 Excel tips Ebook, YouTuber and Excel Blogger.

Recent Posts