A revised version of this content is now available at blog.officewizkid.com
Excel COUNTIF is one of these functions that are simple enough to understand the concept but make you scratch your head when you try to do something less trivial with the condition part.
Sure, simple cases are simple enough:
How to count all the cells in a range A1:A100 that have a specific value called “MyValue”:
But how do you scale to more complex scenarios. E.g. How do you calculate COUNTIF with a condition that references another cell?
Here is a very nice blog with multiple examples reviewing the hidden potential of the COUNTIF function.