One thing that people often want to know, especially if they’re storing lists of things in spreadsheets, is “how many” there are of something.
Fortunately, there are a fair number of ways to get a spreadsheet to tell you how many it has of something. Unfortunately this means it can be pretty confusing when trying to pick the right one for your needs. So, here’s a quick guide as to what functions are commonly available, and what they do. In the next few weeks I’ll talk about them in more detail, including how they overlap and how to emulate one with another. Then I’ll give some examples of how you might use them in the workplace or in daily life.
Sometimes you just need to know how many columns or rows are in a range. The functions for these are pretty intuitive.
- columns(): This returns the number of columns in the range. Columns(B10:Z15) would return 25.
- rows(): This returns the number of rows in the range. Rows(A10:Z15) would return 6.
Counting by type
There are three functions which count a specified sort of thing.
- count(): This returns the number of cells that contain a number.
- counta(): This returns the number of cells that have something in them.
- countblank(): This returns the number of cells that are blank.
All three of these functions can accept one or more ranges, as a sort of shorthand. Saying count(range1,range2,range3) is a shorter way of saying count(range1)+count(range2)+count(range3).
Counting specified values
There are two functions which can look for values that you specify. These values can exact matches (“check”,5,TRUE,pi()), or they can be comparative values (<5, <=3, 6, >7). If you use letters instead of numbers when counting comparative values, it assumes alphabetical/dictionary order. Therefore “bayou” is less than “burn”, which in turn is less than “cantaloupe”.
- countif(): This returns the number of cells that match the specified criteria. It can only check one range of cells
- countifs(): This is the same as countif(), but it can check multiple ranges of cells, for multiple criteria. It isn’t available in older programs.