Here at Enterprise times we get reports that may have thousands of lines of data. There are many times that we need to know just how many times a certain item, person or number appears in the data. One way is to sort in alphabetical or numerical order. Or maybe use the filters to filter your data. Or even use the subtotal function. Or subtotal in a filter. All of these methods have merit in their own way.
In the past I have shown you how to build a count function and gave you more in the tip Count Function Continued. I have also show you how to use the IF function. Here I will show you how to use the IF Function in conjunction with the Count Function.
The actual function we will be using her is called “COUNTIF”. Simply put, it only counts the items if they match my criteria.
So, this is what the COUNTIF function syntax looks like. Using the Insert Function tool.
Double click on this in the list and this dialog box appears.
Range is the area, be it a column or row, where the item that you are interested in resides.
Criteria is what you are looking for. i.e. a name or an item or a number.
Therefore, if I wanted to know how many times a particular person’s name appeared in a list then my formula would look like this.
=COUNTIF(B4:B94,”Peter”)
This formula would look for the name Peter in the column B from Row 4 to row 94 and count how many times it appears.
If I need to know how many times a certain number appeared then my formula would look like this.
=COUNTIF(C4:C94,”1234”)
or
=COUNTIF(C4:C94,1234)
Later versions of Excel recognise both values and text that match both of these formulae.
There is ALWAYS more than one way to get your results. It just needs you to know which way will be most expedient or useful in the long run because of what you might do with the data after you’ve found it.
I wish you happy counting!
