Today's Excel tip for those less experienced users is how to use the COUNTIF function.
This function can be extremely helpful if you have a large data series and want to know how many times a single value occurs.
(More experienced users may wish to use (or prefer) pivot tables rather than the COUNTIF function in some instances.)
The examples below use the following fictitious monthly median house price data.
Jan-12 $360,000
Feb-12 $380,000
Mar-12 $400,000
Apr-12 $395,000
May-12 $410,000
Jun-12 $420,000
Jul-12 $380,000
Aug-12 $401,000
Sep-12 $377,500
Oct-12 $402,000
Nov-12 $400,000
Dec-12 $440,000
Jan-13 $436,000
Feb-13 $440,000
Count how many times between January 2012 and February 2012 is the median house price $400,000?
After setting the data up in excel with the dates in Cells A1:A14 and the data in cells B1:B14 in cell B15 we type =COUNTIF(B1:B14,"$400,000") and the result returned should be 2.
We can see that in Mar-12 and Nov-12 the median house price was exactly $400,000. It is important to put the criteria i.e. $400,000 in a set of double quotes "" otherwise the formula will not work as excel would normally treat the comma between the $400 and 000 as a separate command.
Count the number of times the median house price has been above $400,000?
Again in cell B15 we would type the formula as =COUNTIF(B1:B14,">$400,000"). The result is 7.
You could also write the formula as =COUNTIF(B1:B14,B3) where B3 = $400,000 in the spreadsheet (and you don't put the B3 in double quotes in this case).
To find out how many times the median house price was less than $400,000 we would replace the greater than symbol (>) with a less than symbol (<).
We could want to find how many times the median house price was $400,000 or greater by typing "=>$400,000" into the formula. The answer we would get back is 9.
As noted above you could also use the cell reference B3 in place of $400,000.
Count the number of times the median house price was above $360,000 but below $400,000.
The COUNTIF formula does not allow us to perform this function as it uses a range and one criteria. Therefore to find the answer we have to combine multiple COUNTIF statements as shown below:
=COUNTIF(B1:B14,"<400000")-COUNTIF(B1:B14,"<=360000") = 4, which we know looking at the data is correct.
We know there are 5 median house prices below $400,000 and one median price of $360,000 so we have to take one away from the other to get the correct result.
Again you could replace the values with a cell reference and either way you should come out with the same result.
Extensions of the COUNTIF function
Below is an illustration of additional ways of using the COUNTIF function.
In Excel 2010 there is a COUNTIFS function which allows for between 2 and 127 range/criteria and this should overcome the issue of having to enter multiple COUNTIF statements.
---------------------------------------------------------------------------------------------
We encourage you to use the comment section below to add in your own tips about this function. Or ask a question ...
We welcome your comments below. If you are not already registered, please register to comment.
Remember we welcome robust, respectful and insightful debate. We don't welcome abusive or defamatory comments and will de-register those repeatedly making such comments. Our current comment policy is here.