Explanation
In this example, the goal is to return the sum for an entire row in an Excel worksheet. One way to do this is to use a full row reference.
Full row references
Excel supports full row references like this:
=SUM(1:1) // sum all of row 1
=SUM(3:3) // sum all of row 2
=SUM(4:5) // sum all of rows 4 and 5
You can see how this works yourself by typing 1:1 or 3:3 into the name box (left of the formula bar ) and hitting return. You will see Excel select the entire row.
Example
To solve the problem in the example worksheet, we can use a full row reference to row 5 with the SUM function like this:
=SUM(5:5)
The result is the sum of all numeric values in row 5. As the formula is copied down, we get a sum for row 6 and row 7 as well:
=SUM(5:5) // sum red
=SUM(6:6) // sum blue
=SUM(7:7) // sum green
As new entries for “Red” are added to the table in rows 5, 6, and 7, the formula will automatically include these new amounts.
Advantages and risks
The main advantage to full row references is simplicity. Simple and very compact, a full row reference will automatically include all data in a row, even when data is added or removed. However, full row references come with certain risks. One risk is that you may accidentally include extra data in a calculation. For example, if you use =SUM(5:5) to sum numbers in row 5, you are targeting over 16,000 cells to the right. If row 5 includes extra dates somewhere far to the right, the numeric values of these dates will be included, and SUM will return an incorrect result.
Explanation
In this example, the goal is to calculate a weekly total using the data as shown. Notice each week corresponds to 5 rows of data (Monday-Friday) so we will need to sum values in every 5 rows. To build a range that corresponds to the correct 5 rows for each week, we use the OFFSET function. To sum the values returned by OFFSET, we use the SUM function. The complete formula in cell F4, copied down, is:
=SUM(OFFSET($C$5,(ROW()-4)*5,0,5,1))
Calculating ranges
The OFFSET function returns a reference to a range constructed with five inputs: (1) a starting point, (2) a row offset, (3) a column offset, (4) a height in rows, and (5) a width in columns. In the example shown, we use OFFSET inside of SUM like this:
OFFSET($C$5,(ROW()-4)*5,0,5,1)
The starting reference is C5 since this is the first value in the data. Notice we use an absolute reference because we don’t want this reference to change later when we copy the formula down. The next argument, rows , is the crux of the problem because this is where we need logic that will figure out the correct starting row for each week.
To calculate the right starting point, we use the ROW function . ROW returns the row number for a given reference or, if no reference is provided, the row number of the cell containing the formula. Because the formula sits in cell F4, ROW() will return 4. We use this fact to create the logic we need. We first subtract 4 as an “offset” because we want to create a zero-based index for reasons made clear below. Then, we multiply the result by 5. This is how this snippet will evaluate in cells F4, F5, and F6:
F4=(ROW()-4)*5 // returns 0
F5=(ROW()-4)*5 // returns 5
F6=(ROW()-4)*5 // returns 10
To finish off the arguments for OFFSET, we provide cols as zero, because we want to stay in the same column. We provide height as 5 because we want a range that contains 5 rows. Finally, we provide width as 1, because we want a range that contains 1 column. After the row logic runs, this is how OFFSET works F4, F5, and F6:
F4=OFFSET($C$5,0,0,5,1) // returns C5:C9
F5=OFFSET($C$5,5,0,5,1) // returns C10:C14
F6=OFFSET($C$5,10,0,5,1) // returns C15:C19
Summing ranges
The final step in the problem is just to sum the ranges provided by OFFSET. This is done with the SUM function . After OFFSET is evaluated, the resulting range is delivered directly to the SUM function, which sums the values in the range and returns a final result. As the formula is copied down into F4:F6, we get the final totals per week:
SUM(C5:C9) // returns 220
SUM(C10:C14) // returns 202
SUM(C15:C19) // returns 285
To recap, OFFSET returns a calculation of the correct range for each week using the row logic explained above. The SUM function sums the ranges returned by OFFSET.
