Problem: I need to add up a range that starts in cell A5 and is C5 rows tall. A formula in C5 is calculating a number and I need to include that many cells in the SUM.
Strategy: Use the versatile OFFSET function. OFFSET will let you:
- Start from a specific cell or range of cells,
- Move a certain number of rows from the starting position,
- Move a certain number of columns from the starting position.
The first three arguments get you to a top left corner cell for the dynamic range. Note that if the starting cell is J10, you can point to J15 with a second argument of 5 or point to J5 with a second argument of -5. In a similar fashion, a third argument of 1 will point to K10. A third argument of -1 will point to I10.
Those first three arguments get you to a starting cell. In this current question, you don't need any of that information, because you know that you are always starting from A5. Your formula will start with OFFSET(A5,0,0,"¦
OFFSET allows you to specify two more optional arguments.
- The fourth argument describes the height of the dynamic range.
- The fifth argument describes the width of the dynamic range.
You can use OFFSET(A5,0,0,C5,1) to return a range that is 1 column wide, and a variable number of rows tall.
Gotcha: In most cases, OFFSET will point to a range that is more than one cell tall. In these cases, you can not simply enter =OFFSET(). You have to use the OFFSET as an argument in another function.
In this case, use =SUM(OFFSET(A5,0,0,C5,1)).
- This formula sums A5:A9.
Change the 5 in C5 to a 3, and the formula sums A5:A7.
- Change C5 to a 3, and the sum range resizes.
OFFSET can be used to point to one cell above the current cell. Why would you go to that hassle when a simple formula does the same thing?
- Who needs OFFSET when =A4 works?
What happens when you delete row 4? The simple formula in column B changes to a #REF! error. The OFFSET formula in column E continues to work.
- If rows might be deleted, OFFSET saves the day.
Additional Details: The starting range can be more than one cell. In the example that follows, the starting range is A4:A11. The third argument of the OFFSET function uses MONTH(A1) to move five columns to the right. This formula will total the column corresponding to the date in cell A1.
- Use OFFSET to move a range n columns to the right.
Gotcha: OFFSET is a volatile function. This means that with every calculation of the worksheet, the OFFSET is recalculated, even if none of the cells in the table changed. Those cells could stay the same for a whole month, yet OFFSET will recalculate every time that you change a cell anywhere in this worksheet. Many OFFSET functions can cause your worksheet to slow down. In many cases, you can use INDEX instead.
Back in the VLOOKUP topics, you read how to use =INDEX( B4:M11,row,column) to return one cell from a range. If you leave out the row argument blank, Excel will return all of the rows. The formula of =SUM(INDEX(B4:M11,,MONTH(A1))) will return an equivalent result.
