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".
some people here still use 2003 (yeah, I know!), which doesn't have COUNTIFS, SUMIFS and AVERAGEIFS functions
ReplyDeletethe way I used to achieve the same result was to use SUMPRODUCT (sometimes I still do, as it's more flexible, but it is slower on big datasets)
the above formula translated to SUMPRODUCT would be:
=SUMPRODUCT((A:A="North")*(B:B>5000))
(note no "s needed in second ½ but beware: any text in B:B will be seen as >5000 - use an extra condition such as ISNUMBER(B:B) to solve this if necessary)
so much for keeping it simple this time!
Jim
Jim
DeleteThanks for the additional information. I used to try to cover the Excel 2003 situation in each post, but eventually decided I was probably confusing the majority of people that had moved on, to cover something for the minority. I used to use SUMPRODUCT in 2003 (and, as you say, still do for some situations). Thanks again for the input.