This week’s topic is COUNTIF
Two weeks ago we looked at the formula SUMIF. This week we will look at a very similar formula called COUNTIF. This formula, as its name implies, allows you to count items based on specific criteria which you select.
Examples are the best way to learn a new formula, so let’s jump right to that. If you still have your example spreadsheet from the SUMIF post go open that file, otherwise you can download the example spreadsheet here.
Once again we have a list of individual sales showing the salespeople and the amount sold. Using the SUMIF formula we were able to calculate the total sales for each salesperson. Using the COUNTIF formula we will calculate the number of sales by each salesperson. Then using simple division we can quickly see the average sales amount per sale.
Start by clicking into cell G3 and typing: =COUNTIF(
You should see this below where you are typing:
COUNTIF has two required inputs explained below:
Range is the range of cells you want evaluated. In our case this is the list of salespeople.
Criteria is the condition or criteria that defines which cells will be counted.
Here is the formula that should be typed in: =COUNTIF($B$3:$B$98,E3). Notice once again the use of absolute cell references (the dollar signs). Using this feature locks our salespeople list so that the formula can be dragged or copied to the other salespeople below.
After inputting the proper formula in cell G3 your next steps are to: 1) Copy the formula down to all the salespeople and 2) calculate the average amount sold per sale. The end result should look like this:
Looks like we need to fire John.
Loading ...