Today we’re going to get all clever and introduce one of the cool functions in Excel, COUNTIF. COUNTIF works in much the same way as some of the other counting functions in Excel, except it has a little decision-making functionality built into it to make it all the more powerful.

Take a look at the table below. How many times does Michael’s name appear?

How many did you get? The answer is 6, but I bet it was a real pain in the backside counting manually through that list. Imagine counting through a list a few screens in size. Ouch.

COUNTIF lets us do this kind of thing easily and through a function. It looks like this:-

**=COUNTIF(Range, Criteria)**

Where range contains the cells you want to count from, and the criteria entered are what you want to count within that range. For our example above, the function would look like this:-

**=COUNTIF(A1:D10, “Mike”)**

=6

You might have noticed that the ‘criteria argument of the function in this case has been entered within double-quotes, as we are looking for text.

So far, so simple, yes? Let’s try the same thing, except this time we want to count how many times the number 25 appears in the table below:-

It’s a little bit more of a pain doing it by eye, isn’t it? COUNTIF makes life simple:-

**=COUNTIF(A1:D10, 25)**

=5

Simples! Notice this time that the number 25 is typed without double-quotes as it is numeric in value. Great stuff, isn’t it? It’s worth noting here that, if you type 25 in double quotes, like this..

**=COUNTIF(A1:D10, “25”)**

…the function still returns 5. Number values can be entered as actual numeric values, or as text. It’s worth remembering this, as in the next part you have to combine both. Using the same list we now want to count how many values are in the grid which are greater than 15. Try entering this:-

**=COUNTIF(A1:D10, >15)**

..and you’ll get an error. This is because if you want to do this kind of thing Excel expects that you enter it as text, like this:-

**=COUNTIF(A1:D10, “>15”)**

Which will return a value of 33. You would use the same method if you were searching for values less that 20:-

**=COUNTIF(A1:D10, “<20”)**

Values greater than or equal to 21:-

**=COUNTIF(A1:D10, “>=21”)**

And so on. Making sense? You can also use cell references as criteria, which adds even more power to your formulas.

And that’s it for now. The COUNTIF function is a simple yet extremely versatile addition to your toolkit and can come in extremely handy in adding some real power to your spreadsheets. Come back for more functions in the series!