From the Not Just Numbers blog:
Today, I am going to cover a quite simple function that has been touched on in previous posts but that I have never covered on its own.
Sometimes, you want to know how many items in a list meet certain criteria. As usual, Excel has a function (or two) to help.
If you simply want to count items in one column (the same column that you want to apply the criteria to), you can use the COUNTIF function.
The syntax is as follows:
=COUNTIF(Range,Criteria)
So, for example, if you had a list of sales and column A contained the sales region, you could count how many sales were in the North region, by using:
=COUNTIF(A:A,”North”)
This assumes an “equals” criteria, but you can use other operators. Say that column B contained the value of the sale, then you could return how many sales exceed £5,000, by using:
=COUNTIF(B:B,”>5000″)
But what if you want to return how many sales exceeded £5,000 in the “North” region?
Here, you can use a more recent Excel function that allows you to apply multiple criteria, COUNTIFS. This works just like COUNTIFS but allows as many pairs of Range and Criteria as you want, so:
=COUNTIFS(A:A,”North”,B:B,”>5000″)
would provide our answer.
And that’s it!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.