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?
Strongly agree with you, Marry. COUNTIF is one of the best functions in excel. I use it very often.
Thanks for posting such wonderful examples of both the variants of COUNTIF.
I have used this function many times to assist with sorting data for marketing campaigns v. Useful