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.

COUNTIF(Range,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

=COUNTIF(A:A,">=2")

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:

=COUNTIF(A:A,">="&A2)

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

Hope this helps in your counting.

• Tweet