• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More  
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » Formula Friday – Count How Many Numbers In A List Are Greater Than The Average Using The COUNTIF Function.

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

April 24, 2020 by Barbara

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

 

 

Filed Under: Blog, Excel Tips& Tricks, Formula Friday, Formulas Tagged With: Blog, Excel Formulas, Excel Tips, Formula, formula friday, Worksheets

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Copyright © 2023 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in

SHARE

Additional Info

Want FREE Excel Tips?

Click on my FREE eBook, its my bonus for joining thousands of others who receive my 3 xFREE Excel tips every month in my Excel Newsletter. Join Us!.

Excel Jobs

Excel Charting

Self Expanding Charts
One Click Charts
Create Quick Dynamic Charts
Easy Combination Charts

privacy policy