I’d like to think that I’m a power user when it comes to using Microsoft Excel, but I do need help from time to time, especially when it comes to automating calculations and tabulating data.
COUNTIF (as well as its counterpart COUNTIFS) is one of my favourite functions in Excel, as it is useful in summarizing the raw data I get and translate it into more meaningful patterns, such as demographical information. The problem I encounter with using this function is when I attempt to count the number of instance a number occurs when it is within a range of numbers. For example, in a column containing Age, I would like to COUNTIF the age falls between 18 to 25 years old. COUNTIF is great for precise numbers, but not so great when it comes to number ranges.
For a case like the above, the formulation I was looking for would be something like:
COUNT range A1:A20 if number in that range > 17 or < 26I thought that the following function would have worked, but it didn’t:
=COUNTIF(A1:A20,AND("<17","<26"))A forum discussion on this matter gave me a pretty good idea on how to do this, but it keeps talking about SUMPRODUCT, which I am not familiar with and currently don’t have the time to study into. For some reason, though, reading the ideas in the forum made me realise, hey, why not count the range of the higher number, and then deduct it with the range of the lower number, like so:
[Count number of Age lower than 26] – [Count number of Age lower than 17]
I was then able to calculate if a number is within range by using the following formula successfully:
=COUNTIF(A1:A20,"<26")-COUNTIF(A1:A20,"<17")
In a glance, I’m sure this makes sense to you, too, if you have ever used the COUNTIF function before. Pretty straightforward, don’t you think?
Check out my other posts: « YES Hip Hop Academy and Jesselton Street Jam Kota Kinabalu / Yii: “yes, it is” the perfect PHP framework for me »