Have you ever looked at the CountIF function in Excel? I think it is one of the most useful functions available for quickly analysing data – and it has become even more useful in the 2007 and 2010 versions of Excel.
In its simpliest form, the COUNTIF function will allow you to count the number of rows which meet a certain criteria. In the following example, I want to count the number of sales greater than £1000.
To do this, I put the following formula into a blank cell, =COUNTIF(G2:G13,”>1000″). This will return the answer of 3. I have had several clients who have used this function to analyse the results of survey data.
In Excel 2007, the COUNTIF function has been made even better! COUNTIFS allows you to count the number of rows based on two or more criteria.
So, in this example, we could count the number of rows where the Total Income is more than £1000 and the product sold was a Monitor. The formula would be = COUNTIFS(G2:G13, “>1000”,C2:C13, “Monitor”). This will return the answer of 2.
What do you think, when would you use the COUNTIF or COUNTIFS functions?