The COUNTIF function in Excel counts the number of cells within a range of cells that meet a given criteria. The Range is the Range of cells you want to count cells and the Criteria is the criteriain in the form of a number, expression, cell reference, or text that defines which cells in the selected range will be counted.
For example the criteria could be the following 13, “14”, B2, “>=10”, or “Apples”
This Excel Tip, using the COUNTIF function will show you how to calculate how many numbers in your Excel list are greater than the average. So what could this be useful for? Well, how about:
- How many students scored higher than the average score
- How many students are paid higher than the average wage
- How many of your sales people made higher than the average number of sales
The list could go on and on, but I have found that it really has a multitude of real world applications. So, once you get the handle on the syntax of this formula the sky is the limit in it’s application.
Let’s look at the syntax of the formula itself
=COUNTIF(LIST RANGE,” > “&AVERAGE(LISTRANGE))
This translates to Count how many numbers in the range of cells which form my list are greater than the average number in the list. Let’s look at example.
In my example, a list of wages for a company can be seen below, along with the respective employee reference numbers in Excel Ref is contained in cells range B2:B10 and Wage is in C2:C10
- REF WAGE
- 677 45000
- 678 48270
- 679 64641
- 680 49040
- 681 57497
- 682 26595
- 683 40875
- 684 53669
- 685 70640
My formula for this example reads like this:
=COUNTIF(C2:C10,” > “&AVERAGE(C2:C10))
You can type the formula straight into the formula bar, or select the cell where you want to type the formula, click the Formula Tab, More Functions, Select Statistical from the ribbon to open up the function drop down list. Select the COUNTIF in the list to bring up the functions dialog box and follow the instructions.
The average wages in my sample of employees is 50692, so my formula will look to analyze how many of the employees have wages higher than the average wages in my list contained in range C2:C10. In this case it is 4 employees which have above average wages. These are employees 679, 681, 684 and 685.