Simple statistics: counting compound things

While countif() can count the number of times a specific thing appears in a set of cells, countifs() counts the number of times a corresponding set of things appears in a corresponding set of locations.

For example, countif() could count the number of times a certain person taught a class, and it could count the number of times a certain type of class was taught, but it couldn’t count the number of times a certain person taught a certain type of class. However, countifs() can.

One important thing to note is that it is only available in recent versions of Excel. I know it’s available in Excel 2007 and Excel 2010, but I don’t have access to Excel 2003 to check. It’s definitely not available in Excel 97, or in Google Docs. It’s also not currently in Open Office. So if there’s any chance you’ll be transferring from a recent Excel to a different program, don’t use it!

The syntax for countifs() is similar to the syntax for countif(). Instead of countif(range,criteria), it’s countifs(range1,criteria1,range2,criteria2,…) and so on. So, for the example above, if you were looking for the number of undergraduate classes that Mrs. Smith taught, and the ranges were named “Teacher” and “ClassType”, the formula would be =countifs(Teacher,”Mrs. Smith”,ClassType,”undergraduate”).

One thing to watch out for is that the ranges all have to be identically sized. If range1 is a 1×5 set of cells, range2 has to be 1×5 too.

Another thing to watch out for is the fact that, while our brains like to line data up, this function doesn’t care. It could be a1:a6 and z20:z25. But if you meant to have the ranges a2:a6 and b2:b6, but accidentally did b1:b5 instead, it’ll accept it but your matches will be all messed up.