Exceljet

Quick, clean, and to the point

Sum through n months

Excel formula: Sum through n months
Generic formula 
=SUM(OFFSET(start,0,0,N,1))
Explanation 

To sum a set of monthly data through n number of months, you can use a formula based on the SUM and OFFSET functions. In the example shown, the formula in G6 is:

=SUM(OFFSET(C5,0,0,G5,1))

How this formula works

In the example shown, we have monthly data for the years 2017 and 2018. The goal is to dynamically sum values through a given number of months, hardcoded as 6 in cell G5. This is done by feeding a reference constructed with the OFFSET function into the sum function.

The OFFSET function let's you build a reference using a starting point, a row and column offset, and a height and width. OFFSET is handy in formulas that dynamically average or sum "last n values", "first n values", and so on. In cell G6, OFFSET is configured like this:

OFFSET(C5,0,0,G5,1)

Translated: from a starting position of C5, build a reference 6 rows x 1 column with no offset. With the number 6 in G5, OFFSET returns the reference C5:C10 to SUM:

=OFFSET(C5,0,0,G5,1) // returns C5:C10
=SUM(C5:C10)) // returns 5775

The formula in G7 is set up the same way. The only difference is the starting point:

=OFFSET(D5,0,0,G5,1) // returns D5:D10
=SUM(D5:D10)) // returns 6380

When the number in G5 is changed, both formulas dynamically update and return a new result.

Year to date variation

Instead of hardcoding a value into the formula, you can use the COUNT function to count existing entries in a given column and return that count to OFFSET. A generic version of this formula would look like this:

=SUM(OFFSET(start,0,0,COUNT(rng),1))

where rng is a reference to a range that contains YTD values.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.