### SUM and the =OFFSET function Function Dictionary

Function Dictionary | Function Examples | Function Categories |

SUM and the =OFFSET function | ||||||||||

Sometimes it is necessary to base a calculation on a set of cells in different locations. | ||||||||||

An example would be when a total is required from certain months of the year, such as | ||||||||||

the last 3 months in relation to the current date. | ||||||||||

One solution would be to retype the calculation each time new data is entered, but this | ||||||||||

would be time consuming and open to human error. | ||||||||||

A better way is to indicate the start and end point of the range to be calculated by | ||||||||||

using the =OFFSET() function. | ||||||||||

The =OFFSET() picks out a cell a certain number of cells away from another cell. | ||||||||||

By giving the =OFFSET() the address of the first cell in the range which needs to | ||||||||||

be totalled, we can then indicate how far away the end cell should be and the =OFFSET() | ||||||||||

will give us the address of cell which will be the end of the range to be totalled. | ||||||||||

The =OFFSET() needs to know three things; | ||||||||||

1. A cell address to use as the fixed point from where it should base the offset. | ||||||||||

2. How many rows it should look up or down from the starting point. | ||||||||||

3. How many columns it should look left or right from the starting point. | ||||||||||

Total | Jan | Feb | Mar | Apr | May | |||||

10 | 10 | 400 | 500 | 600 | 700 | |||||

=SUM(E24:OFFSET(E24,0,0)) | ||||||||||

This example uses E24 as the starting point and offsets no rows or columns which | ||||||||||

results in the range being summed as E24:E24. | ||||||||||

410 | 10 | 400 | 500 | 600 | 700 | |||||

=SUM(E29:OFFSET(E29,0,1)) | ||||||||||

This example uses E29 as the starting point and offsets 1 col to pick out | ||||||||||

cell F29 resulting in a the range E29:F29 being summed. | ||||||||||

910 | 10 | 400 | 500 | 600 | 700 | |||||

=SUM(E34:OFFSET(E34,0,2)) | ||||||||||

This example uses E34 as the starting point and offsets 2 cols to pick out | ||||||||||

cell G34 resulting in a the range E34:G34 being summed. | ||||||||||

Using =OFFSET() Twice In A Formula | ||||||||||

The following examples use =OFFSET() to pick both the start and end of the range | ||||||||||

which needs to be totalled. | ||||||||||

Total | Jan | Feb | Mar | Apr | May | |||||

400 | 10 | 400 | 500 | 600 | 700 | |||||

=SUM(OFFSET(E45,0,1):OFFSET(E45,0,1)) | ||||||||||

The cell E45 has been used as the starting point for both offsets and each has | ||||||||||

been offset by just 1 column. The result is that just cell F45 is used as the | ||||||||||

range F45:F45 for the sum function to calculate. | ||||||||||

900 | 10 | 400 | 500 | 600 | 700 | |||||

=SUM(OFFSET(E51,0,1):OFFSET(E51,0,2)) | ||||||||||

The cell E51 has been used as the starting point of both offsets, the first offset is | ||||||||||

offset by 1 column, the second by 2 columns. The result is the range F51:G51 which | ||||||||||

is then totalled. | ||||||||||

1500 | 10 | 400 | 500 | 600 | 700 | |||||

=SUM(OFFSET(E57,0,1):OFFSET(E57,0,3)) | ||||||||||

The cell E57 has been used as the starting point for both offsets, the first offset is | ||||||||||

offset by 1 column, the second by 3 columns. The result is the range F57:H57 which | ||||||||||

is then totalled. | ||||||||||

Example | ||||||||||

The following table shows five months of data. | ||||||||||

To calculate the total of a specific group of months the =OFFSET() function has been used. | ||||||||||

The Start and End dates entered in cells F71 and F72 are used as the offset to produce | ||||||||||

a range which can be totalled. | ||||||||||

Type in the Start month. | Feb-98 | |||||||||

Type in the End month. | Mar-98 | |||||||||

Total | Jan-98 | Feb-98 | Mar-98 | Apr-98 | May-98 | |||||

900 | 10 | 400 | 500 | 600 | 700 | |||||

1020 | 15 | 20 | 1000 | 2000 | 3000 | |||||

13 | 5 | 3 | 10 | 800 | 900 | |||||

=SUM(OFFSET(D79,0,MONTH(F71)):OFFSET(D79,0,MONTH(F72))) | ||||||||||

Explanation | ||||||||||

The following formula represent a breakdown of what the =OFFSET function does. | ||||||||||

The formula displayed below are only dummies, but they will update as you enter | ||||||||||

dates into cells F71 and F72. | ||||||||||

Formula 1 | =SUM( OFFSET(D79,0,MONTH(F71)) : OFFSET(D79,0,MONTH(F72)) ) | |||||||||

This is the actual formula entered by the user. | ||||||||||

Formula 2 | =SUM( OFFSET(D79,0,MONTH(2)) : OFFSET(D79,0,MONTH(3)) ) | |||||||||

This shows how the =MONTH function calculates the month number. | ||||||||||

In this example the values of the months are 2 and 3 for Feb and Mar. | ||||||||||

These values are the 'offsets' relative to cell D79. | ||||||||||

Formula 3 | =SUM( OFFSET(D79,0,2) : OFFSET(D79,0,3) ) | |||||||||

This shows where the month numbers are used in the =OFFSET function. | ||||||||||

Formula 4 | =SUM( F79:G79 ) | |||||||||

This shows how the =OFFSET eventually equates to cell addresses | ||||||||||

to be used as a range for the =SUM function. | ||||||||||