Hello Excellers, welcome to some more Formula Fun on a Friday. In my scenario today, I need to know how many sales force members earn higher than the average sales. One of the quick ways to do this is using the COUNTIF function in Excel.
The Excel COUNTIF function 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. Finally, the Criteria is the criteriain in the form of a number, expression, cell reference, or text that defines which cells in the selected range to be counted.
So, I have a data set with sales for the last three months. One of the metrics I want to use is to see how many of the Sales staff have above-average sales. A snippet of my data set is below.
Excel COUNTIF Syntax Reminder.
=COUNTIF(range,criteria)
We can tweak this to the following to look at the number of sales force staff that reached above-average sales. Let’s take a look at Quarter 1 with the following COUNTIF example.
=COUNTIF(D3:D13,” > “&AVERAGE(D3:D13))
The formula explained.
The formula takes the average value of sales in D3 to D13 and counts how many values in that range are over that average value. In my example the average value is €166,018.55
So, lets write in the formula to see how many Sales people are above that figure and have achieved over the average. In this data set we have 2.
You can type the formula straight into the formula bar, or alternatively select the cell where you want to type the formula. You can type the formula straight into the formula bar or select the cell where you want to type the formula. Alternatively, 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 dialogue box and follow the instructions.
To conclude, I have shown you how to use the COUNTIF formula with a few examples in this blog post. I hope that these tips will help improve your Excel skills and give you more confidence in using formulas for data analysis.
More Excel Information.
Finally, for more information on learning about Excel or improving your skills, click the links for recommended Excel courses. In addition, sign up to my FREE newsletter for three Excel Tips every month. You will receive my FREE Excel tips EBook of my top 50 Excel Tips. Just fill in the form for instant download. Thanks for joining us.
