Excel Snippets: Offset Formula to Calculate Range on Number

MicrosoftI might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

This is based on an example a colleague discussed with me for dynamically calculating the sum of a range of columns based on the user specificed number of months.

The data for the months of the year was going across the worksheet with a total at the end, but he only wanted to total up the rows up to the required month. The example below shows the required output, with the user specifying month 7 (July) in cell B1 and the required total value in cell O3 which is the sum of C3:N3:

Example data in Excel

If we’d wanted the whole row, this would have been quite easy to do using the SUM formula:


The problem is though, that the second cell in the range needed to be variable. Fortunately, Excel has another function which we can use; that function is OFFSET:

=OFFSET(cell reference, rows, columns))

In our example, we needed to set the cell reference parameter to B3, the rows to 0 and the columns to B1:


This OFFSET will, when combined with the SUM formula, give us the result we need of summing from B3 through the columns to I3 when month 7 (July) is speficied by the user in cell B1:


What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Leave a Reply

Your email address will not be published. Required fields are marked *