Simple statistics: counting specific things

If you want to count something specific – say, the number of “Yes” entries – then the function you want is countif().

Countif() is, without a doubt, my favorite counting function. I honestly didn’t even know the others existed, or what they did, until I started researching for this series of posts.

It’s easy to see how it might be used in inventories (How many Bujold books do I have?), but it’s honestly great for getting information about the values in any sort of column with a limited number of possible entries. There has to be a limited number of possible entries because it can only handle exact matches. It can’t tell you how many grades are greater than 90, for example – only how many are exactly 90.

But if everything in a column is either Yes or No, the other functions can’t tell you how many of each there are. I also use it a lot when checking things. I’ll first set up a column that uses an if statement to see if that line is correct, and have it say “Check” if it’s not. Then I use countif() to tell me how many lines were flagged with “Check”. It might seem like a trivial thing to do, but it a) lets me know how much manual work I’ve got ahead of me, and b) makes sure I haven’t missed any. That latter one is important when I’m, oh, checking the proof of a 25-page manuscript against the author’s original, to make sure no problematic changes have been introduced in the typesetting process. It was also very helpful when checking 10,000+ e-journals for link accuracy. There is no way I could have eyeballed and been certain I’d caught all of the problems that needed checking.

It’s worth noting, at this point, that countif() is not case sensitive. If you try to count the number of “Yes” answers, it will also could every “YES” and every “yes”. This is relevant because some functions do care whether things are capitalized or not.

Using countif() is easy. The syntax is countif(range,condition). Or, in Open Office, countif(range;condition). It can only handle one range, and one condition. Sometimes I get mixed up as to whether the range or the condition comes first, but I try to remember that countif() counts a cell in a range, if it matches the condition. It’s not grammatically perefect, but it puts it in the right order.

Countif() is available in most spreadsheet programs, including Excel 97 & 2007, Open Office Calc, and Google Docs Spreadsheet. It is not available in Microsoft Works.