The Excel COUNTIF Function

Today’s Excel tutorial uses the countif function to solve a problem that seems simple, but may well have you scratching your head for a while. We’ll be honest and admit that we were scratching ours for a fair bit! Turns out it’s solved by a very interesting use of the Excel Countif function or formula.

A friend got in touch and asked us the following question:

Given these range of numbers, how would one count the number of times a number falls between 50 and 75?

Pshaw!, we said. Hmph!, we sneered. And in tones that might best be described as condescending, we suggested that COUNTIF might work. Said friend thanked us from the bottom of her heart, and all was right with the world. Until said friend called back and said it wasn’t working.

We cleared our throats, harrumphed gently, and allowed that what we meant to say was COUNTIFS, really, and not COUNTIF.

Friend went and attempted to use COUNTIFS, but to no avail.

At which point we commenced our head-scratching. To realize that yes, COUNTIF will not work, because COUNTIF does not accept multiple criteria. and COUNTIFS will not work, because COUNTIFS do use multiple criteria, but on different columns, not multiple criteria on the same column.

And after some diligent head scratching, we came up with  solution for our friend. And a blog post for you, dear reader.

Which is appended below.

So, here’s how you go about. To start with, name the range on which we shall perform our Excel magic:

Then, use the Excel COUNTIF formula, but with a twist.

First, use the COUNTIF formula to identify the number of times “50″ is exceeded.

Then, use COUNTIF to identify the number of times “75″ is exceeded.

Subtract the second COUNTIF from the first.

Attain Excel nirvana.

Simple, wasn’t it? And yet, it’s a pretty powerful application of set theory (remember reading up about it in school?) and COUNTIF. Hope this helps – and as always, if you have some nifty ideas that the world would benefit from – well, let us know about ‘em!