iWork '09: The Missing Manual

Using Cell Ranges in Functions

All of the function examples you’ve seen so far have used cell references with the same comma-separated format as any other argument. That works fine when you have just a small handful of arguments—to add the values of four cells, for example:

=SUM(B1, B2, B3, B4)

But that’s actually longer than just doing it directly with the plus sign:

=B1 + B2 + B3 + B4

And either method becomes hideously long when you decide to add a column of 10,000 values. In the time it would take to type or click all the cells, you may as well just add up the values yourself. Happily, Numbers provides a shorthand called a range reference that lets you point to any collection of neighboring cells—a range. Many functions accept range references as arguments, including SUM, which adds up all the cells described by the range. To provide a range reference, you give Numbers the cell reference for the top-left and bottom-right cells in the range, separated by a colon. (If it’s a single-column range, then your job is to name the top and bottom cells.) B1:B4, for example, describes adjacent cells in a column—a reference to cells B1 and B4 and the cells in between (B2 and B3). These two formulas return the same result:

=SUM(B1:B4)
=SUM(B1, B2, B3, B4)

Even though a range reference is just a single argument, in other words, it actually “unpacks” into several values. You can even string multiple ranges together by separating them with commas just as you would with any other argument. For example, ...