COUNTIF Excel – a function tutorial
The COUNTIF function in Excel is extremely useful for helping you to count the number of instances within a range of data that satisfies a certain condition.
Let’s assume you have a (very) long list of sales or orders. Hundreds or maybe thousands of lines.
COUNTIF function in Excel - Starting Point
Your boss asks you to summarize the information detailing how many times you have sold to individual customers.
Did you sell to Smith & Sons 5 times over the last year or 25 times?
One option is to scroll down the list and count each instance of when a sale to Smith & Sons occured.
Unfortunately this is time consuming and prone to error.
The solution is to use the COUNTIF function in Excel.
Its syntax is =COUNTIF(range,criteria)
where range is the block of cells that you need to summarize. In the above example it is the range B5:B105 which contains the list of all customers under consideration.
The ‘criteria’ is the condition that you want satisfied. For instance, if you want to count the number of sales to Smith & Sons you would write “Smith & Sons”.
The final result would be something like =COUNTIF(B5:B105,”Smith & Sons”)
You don’t have to hard-code Smith & Sons. You can use a cell reference where Smith & Sons is contained.
In our example the formula becomes =COUNTIF($B$5:$B$105,E5) after fixing the range $B$5:$B$105 so that the formula can be copied and pasted.
COUNTIF Syntax in Excel
After copying and pasting the formula in order to apply to the other customers, the final result is the number of times that each Customer has been recorded as having being sold to.
COUNTIF In Excel - Final Result
