The odd syntax of the COUNTIF function

My friend Sharon yesterday was trying to get her head around the COUNTIF function. Its syntax is odd, so I sympathise. Here's why.

COUNTIF takes two arguments: a range and a condition.


The range is the thing that you'll count a subset of. The condition is used to evaluate whether or not to count each entry in that range. If you have a range containing the values 1, 2, 3, 4 and 5, a condition of greater than or equal to two will yield a count of four, for example.

The range is easy to specify. It works like any range that you'll find in any formula. Use dollars or don't, it's up to you.

The condition is a little more tricky. While I kind of understand why, most people find it odd that the condition must be encased in double quotation marks. So the above condition would necessitate ">=2", including the quotes


Not encasing it in quotes would leave symbols that can do dangerous stuff unrestrained in the middle of formulae, which would be tantamount to madness. But on the other hand, I don't think I've ever seen such behaviour in other walks of programming. It's almost as if you want it to print: >=2

It gets even more odd when you need to put arguments into the condition. For example, instead of referring to a 2, you may want to refer to cell A2:


This concatenates the value of cell A2 on to the end of the ">=" to form the inequality.

Hope this helps in your counting.

This entry was posted in General. Bookmark the permalink.

1 Response to The odd syntax of the COUNTIF function

  1. Betsy says:

    I think everything posted made a great deal of sense.
    However, what about this? what if you added a little content?
    I mean, I don't wish to tell you how to run your website, however suppose you added a
    post title that grabbed people's attention? I mean The odd syntax of the COUNTIF function | Wizard of Excel is a little boring.
    You might peek at Yahoo's home page and see how
    they write article titles to get viewers to open the links.

    You might add a related video or a pic or
    two to grab people interested about everything've got to say.
    In my opinion, it could make your blog a little livelier.

Comments are closed.